Merge Queries Scrambles existing data upon 'expanding rows'

[DBa]

Hi,

I'm currently facing an error in excel power query, however I don't know where else to post. 

Basically I am trying to merge two tables by a numeric key, both being perfect match. However, as soon as I expand one of the merged columns, the initial table sort of randomizes it's rows, completely jumbling up the data. Since it's excel, i double check and vlookup works perfectly by using the numeric key.

In a different table where i had an identical issue, by buffering the table (table.buffer) before expanding the rows, it fixed it. However this query is too complex and runs out of memory.

Did anyone else encounter this issue before/has managed to fix it? I couldn't find anything else on the internet.

Thanks

[Anonymous]

I believe this is still an issue as I found a few separate forum posts describing the same thing and I am currently experiencing something similar.  Table.Buffer does seem to work but as mentioned, it drastically slows down the model.

[v-yuezhe-msft]

@DBa,

About Power Query issues, please post the question in Power Query forum to get better support.

If you still get same issues in Power BI Desktop, please share dummy data of your table and post screenshot about your scenario for us to analyze.

Regards,
Lydia

[DBa]

@v-yuezhe-msft, 

Thanks for the reply, I managed to recreate this error in power bi,  how do I attach screenshots?

Basically I have a table of format Customer ID/Date/Details/Order ID. Based on Customer ID and Date I create an index using below formula, so that Customer orders are ranked from 1(oldest) to x: 

= Table.Group(#"Sorted Rows", {"Customer ID"}, {{"Count", each Table.AddIndexColumn(_,"Index", 1, 1) , type table}})

I then merge with another table based on Order ID (no duplicates, only unique values) and after 'Expanding Columns' the initial part of the table changes date order from newest to oldest and is re-ranked as such, the newest value being the rank 1 now.

Is it something related to buffering the table at some earlier point in the steps?

Thanks

[luisamboni]

any solution?