Merge changes a string formed with Text.Combine

[Anonymous]

Hi, everyone!

I have a series of events that I am combining in a single string in order to be able to use the PATH... functions to examine them -- the events have a code that differentiates them, that's what I'm concatenating. 

The chains of events are created using Text.Combine, after sorting them using a unique combination of values and grouping using the id of the entity to which the events are related.

#"Sorted Rows1" = Table.Sort(#"Changed Type",{{"entityNumber", Order.Ascending}, {"key", Order.Ascending}}),
#"Grouped Rows1" = Table.Group(#"Sorted Rows1", {"entityNumber"}, {{"Table", each _, type table [key=text, eventNumber=text, eventCode=text, eventDate=date]}}),
= Table.AddColumn(#"Grouped Rows1", "chainEvent", each Text.Combine ( [Table][eventCode], "|"))

This works very well. Here is an example of a chain of events according to their codes. They are in the correct order in which they were sorted.

02.10|02.01|03.01|03.08|06.06.01|06.01|09.01|16.01

However, this is done in a table I don't really need, and to decrease the number of tables in the model, I wanted to merge it into the one comprising the entities to which the events pertain, and simply do not load the first table.

This was my intent. But, to my surprise, after the merge, the strings in the new column in the Entities table had changed. The order of the events was scrambled. Actually, I checked and the order had been returned to what it was before the sort.

06.01|06.06.01|02.10|09.01|02.01|16.01|03.01|03.08

The palliative solution I found was keeping the table with the calculated strings and setting a relation to the Entities table. Before the merge, the order is correct, so I have no problem in using the strings that are there.

Although the model is working perfectly with this one more table, it seems to me that this is a bug that should be addressed.

Any thoughts would be welcome. Thank you!!

[EmmanuelD]

Hi, just stumbled on this thread as I am having the exact same issue. Not sure why but adding a Table.Buffer step at the end of your first query seems to "lock into place" the order of the combined fields: you should be able to further use it in Merge queries without unwanted swaps.

Hope this helps!

[Greg_Deckler]

@edhans  and @ImkeF this sounds like a weird one for you.