Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Pivot on Multiple Dimensions and Values

I have a sales table with the following columns:

 

* Date

* Product ID

* Sales Value

* Profit

 

Because this comes off an invoicing table, there are multiple entries for each date/product ID combination. I want to combine these, so that each date and product has only one line, with the sales value and profit summed for each. How do I do this in the Power Query Editor? 

1 ACCEPTED SOLUTION
parry2k
Super User
Super User

@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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

10 REPLIES 10
amitchandak
Super User
Super User

@Anonymous , Why do you want that in power query.  Save this and use this visualization tab. You just need to take a sum of profit and sales. (Default for numbers is sum)

Use table, Matrix or any other visual. That should be done.

 

refer this video - https://www.youtube.com/watch?v=m1eLTtZHGs4&feature=youtu.be

@amitchandak what if they want to also include a count, or a daily average? Won't that make the DAX more complex? I know doing a SUM really makes no difference. 

 

I would have thought other aggregations would be more difficult if the data was at a different granularity.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

@edhans you this line in the signature is saying everything DAX is for Analysis. Power Query is for Data Modeling

 

Regardless, it is sum or avg or count, I don't see any value of doing the aggregation in power query specific to this particular post. 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Thanks @parry2k - I guess I am not clear then. Say I have this data:

Date Invoice Product Amount
7/10/2020 123 A 88
7/10/2020 123 C 29
7/10/2020 123 J 37
7/10/2020 455 E 78
7/10/2020 456 K 94
7/10/2020 456 A 35
7/10/2020 456 F 84
7/10/2020 678 G 21
7/10/2020 678 I 42
7/10/2020 678 F 61
7/10/2020 999 G 86
7/10/2020 999 D 56
7/10/2020 999 B 85
7/10/2020 999 D 98
7/10/2020 999 D 23
7/10/2020 999 I 27

 

If I want total sales by product, that is easy, as you said.
If want to the average daily sales for each product though, wouldn't I need to first sum the products by day, then do an average? Whereas if the data granularity was at the daily level and the invoices were removed, both the sum and average would be equally easy.

Also, if I wanted to get a count of how many days "D" was sold, I would have to wrap the count in DISTINCT whereas if the invoices were gone, I could just use COUNT, or COUNTX. 

The reason I have Power Query is for modeling and DAX is for analysis, I use them equally, and I use Power Query to get my data in the granularity I need, and, like @Anonymous I almost never care about invoice level detail, so I do similar modeling to summarize by daily, or even monthly, activity, before bringing it in to DAX.

But where am I missing what  you are saying here? FWIW, I am working my way through SQLBI's DAX Modeling course and having data at the right granularity to support the analysis you want to provide seems to be a key point in many of their lectures, but I am still learning, so what would be the advantage of having more detailed granularity in the data than is needed by the analsysis just because it comes from the source system at that more detailed level?

 

Thanks!



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

@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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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. 👍



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
edhans
Super User
Super User

In Power Query:

  1. Select the Date and Product ID columns
  2. On the home ribbon, select Group By
  3. In the bottom section add the Sales value, call it Sales, Sum operation, and Sales column
  4. Add an aggregation, then do Profit, Sum Operation, Profit column.

It would look like this, but with your columns.

edhans_0-1594396843712.png

It is good you are doing this. Having data at the right granularity is key to a good data model, and if you don't need the duplications of dates and product IDs, Power Query is the best way to do this.

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
parry2k
Super User
Super User

@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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

calerof
Impactful Individual
Impactful Individual

Hi @Anonymous,

 

Providing a sample of your data would help. Also, you seem to have missing some columns in your fact table. How do you know to what invoice number does each record correspond?

F

 

Anonymous
Not applicable

samworth-andrea_0-1594396770649.png

 

An example of the data is above. I don't want the invoice ID at all, I've already deleted it. 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.