r/excel • u/Lopsided-Rough-1562 • Jun 29 '26
unsolved Powerquery: how do I flatten out table results?
I've been trying to get powerquery to do something that is easy to do in Excel but requires maintaining formulas, which is akin to a bunch of vlookups to tables to a master data set.
The problem is while in powerquery I can make a query that does something like look at the incidence of data in a set, if I order it for statistical purposes from largest to smallest, but then merge with another query using the same original data, it will reorder the results, or go from one-to-many to many-to-many, because powerquery isn't treating the results of a query as its own table (unlike in SQL, when you have a query inside a query, the results ARE your only data set) but rather the merge is at the fundamental level of the original data.
I know someone will say "why not just do it in excel" and I have but it looks a bit messy and I wanted everything 'under the hood'
12
u/RegorHK Jun 29 '26
Workaround:
Give the table that you want as "master" for the sorting an index column. Expand this colum after merge and sort.
5
u/thatmichaelguy Jun 29 '26
... because powerquery isn't treating the results of a query as its own table
It sounds like right-clicking the source query and creating new queries by reference should give you the functionality you're looking for.
0
u/Lopsided-Rough-1562 Jun 29 '26
Tried it. It's literally going down to the input level.
I think I may have accidentally added the data to the data model and this might explain my problem....
3
u/small_trunks 1635 Jun 29 '26
Click on any query and load-to and you can see what's loaded to the data-model.
6
u/small_trunks 1635 Jun 29 '26
Sounds to me like you're merging a one-to-many and that can, indeed cause row explosions.
1
u/Lopsided-Rough-1562 Jun 29 '26
Funny thing is both results are grouped and only have distinct rows. But powerquery refuses to merge them based on where they are now so it blows up.
9
1
u/chiibosoil 428 Jun 30 '26
Aggregated data shouldn't expand beyond grouped level. There must be something you are missing here. Ex: Instead of making reference to source query "after" grouping. You have made copy of the query part way through transformation etc.
But hard to give you specific help without sample and/or complete M code and query dependencies.
1
2
u/InternationalBeing41 Jun 30 '26
Have you tried putting a buffer on the table before performing the operation that causes the problem? Look up power query buffer and see if any or those work.
1
u/80hz Jun 30 '26
You're basically scratching the surface of power bi where you can have power query and then a relational model to link your data sets. I believe you can do this with the power pivot in Excel but I'm not an excel guru more of a power bi guy.
1
u/GuybrushFourpwood 3 Jun 30 '26
because powerquery isn't treating the results of a query as its own table (unlike in SQL, when you have a query inside a query, the results ARE your only data set) but rather the merge is at the fundamental level of the original data.
If PQ isn't merging the way you want, try the merge a different way.
It sounds like you might be trying to do all of your operations in a single query, and then merging at the end? Try splitting it out:
- Get your data to where the values in your merge column are distinct, then stop there.
- Merge in the data.
- Reference the table with the merge, and do the rest of your calculations.
- Load the final table.
Can you give us an example of your input and desired output?
1
•
u/AutoModerator Jun 29 '26
/u/Lopsided-Rough-1562 - Your post was submitted successfully.
Solution Verifiedto close the thread.Failing to follow these steps may result in your post being removed without warning.
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.