Index - Breaks datamodel

[jjuarezh]

Hi everybody,

So I have a multiple excelfiles, and I need to create a report based on that, the process basically consist on getting the common data shared between the files, and separate them. To keep the relationship between rows and values I add a index column with each file using the powerBI index functionality, at the beggining it was working fine, but suddendly it seems like the index is not matching the values anymore.  (I'm using the index as a foreing key to keep realtionship).

So the data looks like this

Original data:

Index	Prod	Color	Date	Price
1	A	Blue	010101	1
2	B	Yellow	020101	2
3	C	Red	030101	3

After separation

Table 1

Index	Prod	Color
1	A	Blue
2	B	Yellow
3	C	Red

Table 2 

Index	Date	Price
1	010101	1
2	020101	2
3	030101	3

This is how it was behaving but suddendly is mixing the index values.

Can you help me to find a way that once the index is created, it doesn't change afterwards?

I already tried this, it worked for a table but for the rest it doesn't.  : https://powerusers.microsoft.com/t5/Power-Query/Change-in-Index-After-Expanding-Merged-Table-Columns...

Additionally, and merging 6 tables.

Thanks

[jjuarezh · Accepted solution]

I solved it,

My issue was that I was duplicating the table instead of refering to the original, so when I came to the creation of the index column, it recalculated the index twice for the two different tables and at the moment of trying to make the relationship between tables.

Thank

[v-junyant-msft]

Hi @jjuarezh ,

Could you please provide a screenshot of when the "suddendly is mixing the index values." scenario you are referring to occurs? This could be due to an issue with the data model itself, or it could be due to some operation in Power Query.
Before adding indexed columns, make sure the data in the table has a stable sort order. A stable sort order will ensure that the indexes remain consistent when refreshed.
After adding an index, avoid operations that may cause the index to be recalculated, such as removing duplicates, sorting, or other transformations that change the order of rows.
If further transformations are required, refer to the query to which the index has been added, rather than starting the transformation from scratch.

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.