Forum Discussion
Turn rows into columns
Hi friends,
I have made a power query that takes data from a folder, each month I add an excel with the cumulative financial of a company. Every new excel in the power query table is added as new rows in my dataset. I want to convert those rows (every new batch is a new month) into columns. And every new month will added as new column in the future and not as rows. So my final power query table looks like that :
- Anonymous
If you pivot the data then your column to column calculations will NOT update automatically - you will need to redefine the calculations each month to include the new month's data. If you leave the date values in a single column, then you can use DAX Time INtelligence measures to calculate the differences between Years, Quarters, etc.
See if these posts help:
A date table can make quarters, year to date, etc comparisons easier, then use functions like TOTALYTD, DATEADD, etc:
https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html
Or without a date table see this post:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/td-p/434008
For updating your report with new Excel file each month, see if this helps:
https://www.myonlinetraininghub.com/power-query-get-files-from-a-folder
It's written for Excel, but Power BI has the same functionality, just different looking buttons, so see this post for what it looks like in Power BI, but Mynda in the link above explains things really well.
https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-combine-binaries
5 Replies
Anonymous Please can you explain the final requirement, I'm not sure why you need this done in Power Query?
It can be done using the 'Pivot' button in the Transform tab. See attached file for reference. HOWEVER, I do not recommend this approach as a general rule and suggest doing the Pivot operation inside the matrix visualization as you can also see in the attached report.
Hey Alison,
Thanks a lot for your answer!
My end goal is to make a financial report that looks like this picture. I came up with this idea in order to be easier for me to make caclulations between columns (e.g Quarters, 6-month period) also my data depict the whole financial period and not only for the current month (i.e. the excel of June has financial data of Jan+Feb+..+June) and I was thinking that with the idea that I proposed I would easily caclulate the change between months (June - May = Change in June). Also, I wanted the whole process to be automated so the user can only copy paste the new excel on the folder and the report to be raedy and updated. So I was thinking of doing the whole preprocess on the power query editor.
Any suggestions, ideas, questions are welcome! 🙂
- Anonymous
If you pivot the data then your column to column calculations will NOT update automatically - you will need to redefine the calculations each month to include the new month's data. If you leave the date values in a single column, then you can use DAX Time INtelligence measures to calculate the differences between Years, Quarters, etc.
See if these posts help:
A date table can make quarters, year to date, etc comparisons easier, then use functions like TOTALYTD, DATEADD, etc:
https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html
Or without a date table see this post:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/td-p/434008
For updating your report with new Excel file each month, see if this helps:
https://www.myonlinetraininghub.com/power-query-get-files-from-a-folder
It's written for Excel, but Power BI has the same functionality, just different looking buttons, so see this post for what it looks like in Power BI, but Mynda in the link above explains things really well.
https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-combine-binaries