Forum Discussion
Change in Index After Expanding Merged Table Columns.
Before you do the merge, wrap both tables in Table.Buffer() and see if that helps. To do that:
- Go to each table
- Go to the last step
- Go to the formula bar. Whatever is there, add "Table.Buffer(" on the front, and another ")" on the end.
- Repeat with 2nd table
- then do the merge.
What I suspect is happening is your index is based on how data is sorted, and sorts in Power Query can be affected by other data, like data coming from a merge. Table.Buffer protects from that. It also breaks any Query folding, so if you are connecting to a SQL DB or other RDBM, be aware of that potential performance hit.
Before you do the merge, wrap both tables in Table.Buffer() and see if that helps. To do that:
- Go to each table
- Go to the last step
- Go to the formula bar. Whatever is there, add "Table.Buffer(" on the front, and another ")" on the end.
- Repeat with 2nd table
- then do the merge.
What I suspect is happening is your index is based on how data is sorted, and sorts in Power Query can be affected by other data, like data coming from a merge. Table.Buffer protects from that. It also breaks any Query folding, so if you are connecting to a SQL DB or other RDBM, be aware of that potential performance hit.
That worked! Thanks for the assist.
Life-saving thank you so much
Your Solution worked for me.
Now on expanding the merged table. Index column values are as expected.
Thank you so much! This saved me!
edhans Could you give additional insight on this as well as when is the best time to add the Table.Buffer step? I'm having the same thing happen, and I sort the data by Ascending Created Date right when I load it in from the data warehouse. Do I have to add a Sort/Buffer step after every merge? Just at the end? It's almost like its shifting and/or re-evaluating.
👍It worked for me. A quicker solution is to add Table.Buffer() to the step prior to expanding the columns.
