Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hello,
I have encountered a strange thing when I expand merged table column.
I use JoinKind.LeftOuter to merge queries - that should keep all the rows from the first/left table - and it does.
But when I expand the column I loose 2 out of 719 rows. How is that possible?
Here is a gif with only 2 filtered rows, but it shows the behavior:
the table contains personal details so I cannot share it or show more columns but it is always the SAME PERSON that disappears after expanding.
EDIT:
I wanted to add a custom column showing the row count of the expading table, but after this step, the row DOES NOT dissapear:
Any explanation please? I really dont understand that and it really scares me that I might be loosing rows in my other queries!
(the same thing happens in Power Query Excel)
Warm regards
Jakub
Solved! Go to Solution.
Hello,
I have been told that this this "this behavior is intentional. Operations like “distinct” and “merge” do not guarantee that an input sort order is preserved. This is because in cases when we run the operation against a relational database, the database itself does not preserve the sort order."
Here is some help text about it: https://docs.microsoft.com/en-us/power-query/commonissues#preserving-sort
Personally I don't quite understand nor argee how this is an intentional behavior, when a preview in one step is showing different values then in the next step.
Seeing steps in the right pane is giving anybody a false understanding of how power query process data: Not in sequentional steps as seen in the steps pane, but in a different hidden way that cannot be predicted.
This feature has already caused me a lot of trouble. So be aware!
Hello! I might be late a bit to the party, but I ran in the same situation. My database is kind of large and complex.
What worked for me:
Sort step = Table.Buffer(Table.Sort(#"Renamed Columns",{{"FP&A Department", Order.Ascending}}))
Merge step: = Table.NestedJoin(#"Sorted Rows", {"FP&A Department"}, #"FP&A Key", {"FP&A Department"}, "FP&A Key", JoinKind.LeftOuter)
Expand Step = = Table.ExpandTableColumn(#"Merged Queries", "FP&A Key", {"Allocation Key"}, {"Allocation Key"})
For me, it helped with:
Just sharing this solution which worked for me in case it can help someone else, while all the other options are working as you`d like to.
Another 2 years later. For me Table.Buffer messed the merging step up, before even expanding.
I managed to solve it by doing the merge query and expansion at the beginning of all the steps.
Doesn't make sense at all, cost me hours of frustration.
How can I ever trust my data again...
Hello,
since I encountered this problem and its cause, I am always cautious when using Distinct+Sorting+Merging in a query
I found that inseting a index colum before the exanding step seems to fix the issue.
I realize this was posted 4 years ago, but I just had to say this was a great solution! This problem was driving me crazy and I could not for the life of me figure it out. Thank you!
Legend. Thank you! This was making me crazy!
hi, @jdusek92
I have tested on my side, but not reproduce the issue.
I think there should be something wrong with your JoinKind.LeftOuter to merge queries,
Could you please check if the matching column from two tables could be mached.
If not your case, please use virtual data or some sample data to create a simple sample pbix file for us have a test.
Best Regards,
Lin
Hello,
unfortunately I cannot share sample data - the source data is pulled from ACCESS db.
when I try to store sample data in Excel table, the problem does not occur.
Clearing cache did not help
Anyways I tried to use Table.Buffer in Expand step:
Please see the gif below that show different results with/without Table.Buffer:
Could anyone please explain different results?
Does it mean that the basic merge/join feature is that unreliable and I have to go through all my queries (dozens) and check for lost rows?
Warm regards
Jakub
hi, @jdusek92
From the screenshot, there is no match value in"KATALOG" table, so that leads to this issue.
eg. there is no "999" in idPos column of "KATALOG" table. Therefore it returns null value.
https://docs.microsoft.com/en-us/powerquery-m/table-join
https://docs.microsoft.com/en-us/powerquery-m/joinkind-leftouter
Best Regards,
Lin
Hello,
for anyone wondering - it is a bug and I have provided MS developers with a sample file:
https://drive.google.com/file/d/1SC0o3eO_HftVtJ-imU0bUc6XHj_b0dsW/view?usp=sharing
After few hours I was able to prepare a sample excel file that carries the source data, queries and also the BUG.
query “FINAL” contains the BUG in the last step.
I have also discovered a new fact:
Looking at the things that help, it seems to me that the query needs to be “kicked” to “recalculate” with functions like Table.Buffer or Table.RowCount that forces the query to do it.
I found out the same issue by mere coincidence (I was missing some data and went looking where it went). Two years have passed and the bug is still here.
Thanks for this, unfortunately the Table.Buffer techinque was making my query run incredibly slowly and adding the row count column didn't seem to do anything.
I also tried changing the merge type to a Full Outer (full rows from both tables) then filtering out the rows added from doing the Right Outer part of the merge. Came back with the same number of rows 😞
This is in Excel Power Query (on Office 365) rather than in Power BI but the principles should be the same.
Hello,
I have been told that this this "this behavior is intentional. Operations like “distinct” and “merge” do not guarantee that an input sort order is preserved. This is because in cases when we run the operation against a relational database, the database itself does not preserve the sort order."
Here is some help text about it: https://docs.microsoft.com/en-us/power-query/commonissues#preserving-sort
Personally I don't quite understand nor argee how this is an intentional behavior, when a preview in one step is showing different values then in the next step.
Seeing steps in the right pane is giving anybody a false understanding of how power query process data: Not in sequentional steps as seen in the steps pane, but in a different hidden way that cannot be predicted.
This feature has already caused me a lot of trouble. So be aware!
Thank you for your response.
As far as I understood the article you sent me, merging a table deletes or ignores the sort order. However I find that in my data set, the row numbers don't match (before and after the operation). Was this the case in you scenario ?
Andrei
Yes, that could be similar to my scenario.
Try putting Table.Buffer to the Merge Step - not around the whole step, but around the merging tables
hi, @jdusek92
Thank you for your feedback, I have reported this to power bi product team, if there is any update, I will post here.
Best Regards,
Lin
Hi to everyone!
Im just reporting that the issue still exist. Thanks to @jdusek92 for the table.buffer solution it helped me a lot.
Best Regards,
Campagna Cristian
Operational Data analyst on Aerolineas Argentinas
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
87 | |
82 | |
71 | |
49 |
User | Count |
---|---|
143 | |
123 | |
107 | |
61 | |
55 |