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!
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.
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.
Did you ever found a 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!
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