Many Excel reports contain monthly sections such as Jan, Feb, and Mar mixed together in a single worksheet.
This type of structure is difficult to analyze because the data is not arranged in a proper table format.
In this example, Power Query automatically:
- Detects month header rows
- Fills month values downward
- Cleans supplier data
- Unpivots the report structure
- Combines month and attribute names
- Creates a dynamic Pivot Table format
The final result converts a messy report into a clean analysis-ready table.
This method is useful for purchase reports, supplier analysis, monthly summaries, and Excel dashboard preparation.
videolink - https://youtu.be/ZqEUx-8vXuo
Download the sample file below.
You can download the zip file in [Download] page
The file name is 44-2.powerquery_pivot_monthly_report.zip.
Use the English M code provided for your dataset.
You may need to manipulate the M code under your circumstance. You consider this point and use M code
below txt file is detailed-explanation. You use M code in reference with this explanation.
---------------------------------------------------------
Need a custom automation for your Excel workflow?
I can build a tailored solution based on your data.
✔ Used in real-world business automation
✔ Saves hours of manual work
minbedata@gmail.com
You can pay via PayPal.
You don't worry about conversing with me.
My Major is an English literature, so I can speak English fluently and read, listening, writing proficiently
korean version web address (한국버전) is
<a href="https://blog.naver.com/minbe-data" target="_blank">네이버 블로그 바로가기</a>