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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors