Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
jjuarezh
Frequent Visitor

Index - Breaks datamodel

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:

 

IndexProdColorDatePrice
1ABlue0101011
2BYellow0201012
3CRed0301013

 

After separation

 

Table 1

IndexProdColor
1ABlue
2BYellow
3CRed

 

Table 2 

 

IndexDatePrice
10101011
20201012
30301013

 

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

1 ACCEPTED SOLUTION
jjuarezh
Frequent Visitor

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

View solution in original post

2 REPLIES 2
jjuarezh
Frequent Visitor

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
Community Support
Community Support

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.

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Top Solution Authors
Top Kudoed Authors