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.
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.