Merging not behaving correctly

Hello to AlienSx  and Anonymous 

so, after some testing I realized there is probably a bug in Powerquery in a previous step.

The Index column you see in the table was generate previously following these instruction:

https://radacad.com/create-row-number-for-each-group-in-power-bi-using-power-query

then when merging the two tables together based on the Key (which is depending on the Index) it gave me the bug I reported.

It seems powerquery recalculates the index based on some kind of logic of its own. In fact, before running the index I sorted the data in a specific order. when the merge occurs however, the Index ranking somehow ignores the previous sorting step. this is why we see different numbers.

Solution:

I figured out a solution.

Basically, I sorted the data, then created an index before groupping the data.

When I then groupped the data, I calculated for each set of table the first index value.

Then, I expanded the column contanining the tables and I substracted from the original index value the "Min Index". I now have a Index by category and it does not give me the problem mentioned above.

some details of the solution adopted:

https://community.fabric.microsoft.com/t5/Power-Query/Max-value-from-each-table/m-p/3307976#M106786

regards