Forum Discussion
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:
| 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/td-p/1174178
Additionally, and merging 6 tables.
Thanks
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
2 Replies
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.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
