Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 :
Solved! Go to Solution.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
Hey there,
In transform data tab go to transform > pivot column.
Make sure the column date is used in the columns and the value column is used in values:
Result:
Eric.
Quality over Quantity
@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.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
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! 🙂
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
Thanks a lot Alison! I'll go check all the info you send me and (hopefully not) come up with questions.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
61 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |