Forum Discussion
Power Query - Row disappear after expand column
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!
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:
- Expand Column step does not only remove some rows. But also the resulting rows are completely different – it returns back rows that have been removed in previous steps. (see the attached gif)
- As I was able to place everything in Excel file that means that it has nothing to do with the Access connector
- My guess is that the combination of sorted rows and remove duplicates is to blame
- If I add Table.Buffer in the Expand Column it works fine
- = Table.ExpandTableColumn(#"Merged Queries", "Query1", {"Column1"}, {"Query1.Column1"})
- = Table.ExpandTableColumn(Table.Buffer(#"Merged Queries"), "Query1", {"Column1"}, {"Query1.Column1"})
- If I add a custom column BEFORE Expand Query:
- = Table.AddColumn(#"Merged Queries", "Custom", each Table.RowCount([Query1])) - DOES HELP
- = Table.AddColumn(#"Merged Queries", "Custom", each "SSSS") – DOES NOT HELP
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.