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.
Thank you edhans for your patience, explanations, and workaround!
I just wanted to point out that following the transformation steps I mentioned above, even if we don't know "when" the index is created, as long as it is created only once, there shouldn't be any problem. The fact that we sometimes end up with scrambled indexes in both tables (with a common ancestor where the index is supposedly created) seems to indicate that the index is actually being created twice, once for each one of the two tables, which could then result in non-matching indexes.
That is quite counterintuitive when you base your transformations on the principle of causality.
I agree with you that ideally the data should come already normalized from the backend, but sometimes you have to work with long flat tables in PQ and need to normalize them a bit in order to have a manageable model. As you mentioned if the tables grow too much there might not be an alternative to normalizing on the backend.
Since I haven found much information about this anywhere except on this thread, it's probably not identified as a bug yet, so it may take a long time to fix.
Thankfully we can work around it with your Buffer recommendation (even if that may cause other problems).
Cheers!
It is not a bug. I can assure you of that. It might not work like you want it to, but it isn't a bug. Read this article and watch this video by Ben Gribaudo. You'll understand the data in Power Query is transient, and it is never all of the data like you have with a relational database. Until you use Table.Buffer. But you can hit memory issues depending on the size of your table.
How Power Query Thinks: Taking the Mystery Out of Streaming and Query Folding (Video) | Ben Gribaudo