Forum Discussion

jdusek92's avatar
jdusek92
Advocate III
7 years ago
Solved

Power Query - Row disappear after expand column

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 ...
Need Help
  • jdusek92's avatar
    jdusek92
    6 years ago

    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!

     

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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.

  • In my case, using Table.Buffer with the expand step didn`t work, since it slowed it down. My PC froze as well.
  • Adding an index column before the expand step worked, but again it was slowing down my querry a lot.

What worked for me:

  • I always sort used in the merge process in same direction (ascending/A-Z etc)
  • This round, I added in a Table.Buffer in fron of the Table.Sort, and the merge was successful.

 

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:

  • Preventing the merge process to create "null" entries on the existing rows
  • It was finally pulling all rows from FP&A Key table (FP&A Key table had 3 rows matching to my main table, but due to the issue it was not pulling all of them)

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.