Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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
Solved! Go to 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
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
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.