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...
- 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
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
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
Thanks a lot Alison! I'll go check all the info you send me and (hopefully not) come up with questions.