Forum Discussion
Pivot on Multiple Dimensions and Values
Anonymous first you don't need to sum these in power query, you can load raw data and just sum in your visual. If you still want to do grouping in power query, there is group by option in the menu, pick , date to group by and use two columns to sum in the same dialog box.
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡
edhans Bringing the data to the same granularity is one thing but aggregation is another. If data is already at the granular level along with other facts in the model, I don't see the reason to aggregate in power query, how about if someone wnt to drill down to give the list of the invoices. Again, this all depends on what business question someone is trying to answer (immediate need) and what could potential questions in the future. Once data is aggregated and if there is a need in the future to get more granularity, means you have to go back to your transformation and that can be very very challenging and pretty much going back to square one.
Again, it is not that doing grouping in Power Query is wrong but I will not do this and so far worked with many small/mid/large datasets, maybe only once or twice I have to do grouping Power Query coz of granularity. Keep in mind, the grouping is a very expensive query and on a large dataset, it can slow down the whole loading of the data. Too many factors.
Cheers,
P
Thanks for the insight parry2k .
Yeah, I only do grouping in Power Query to adjust granularity. As to your point on how expensive it, you are right - it can be. I've aggregated CSV files before this way to reduce granularity and the processing time is unbearable. I've done it on tens of millions of rows in SQL Server though and due to folding, it speeds things up as the SQL server does the gouping then sends me a few hundred thousand rows that are summarized by month for example vs daily transactional volume.
Again, much appreciated. Always like other perspectives. 👍