hi,
I have faced a problem with agregating data in Power Query per customer and product in rows and sales in columns per rows. What I keep getting is it generates separate rows for each month per customer per product. What I would need, is to have one single row per customer per product.
I do not want to make pivot table - it has to be in excel table for some reasons.
pls find table attached
In PQ, select the first two columns and launch the Group By dialog. You will need to add a SUM aggregation for each of the remaining columns.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
hi,
the goal is to have it at that stage. The source table will be updated each month, so the problem will remain and keep getting back every month. I am also not final user and they will not use PQ for sure
Sorry - I have no clue what that comment is supposed to mean.
If there is more that we need to know, tell us now. Make the whole scenario absolutely clear.
hi,
If I understand you well, with your solution the same problem will occur every next month. this table will be updated every month, so for example if I add something in march 2020, it will not appear there or I will have the second row with the same customer and product.
The goal is to have it dynamic: columns with months (sales and cash) will appear automatically if needed and no duplicated rows
Try this - it repeats the unpivot function for Sales Qty and Sales Value, then merges the two datasets.
Please Login or Register to view this content.
let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source
If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE
Walking the tightrope between genius and eejit...
works perfectly
the problem is solved, but just one more question - are you able to make the month columns appear in time order?
Sure:
Please Login or Register to view this content.
wow, thanks
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks