Forum Discussion
Relationships deleted on refresh
Has anyone got the solution? I have this same issue now but I can't find any information that could help.
Tks!
I think I've figured this out whilst trying to solve another problem.
When you refresh your Query, sometimes if your datasource is different, or your raw data column name is different, PowerQuery assigns it a different name or some form of metadata that is different. As a result, BI cannot detect the existing relationship as it thinks the old column is deleted and replaced by a new column (even if the adjusted name is the same).
I got around this by creating a DAX column of all neccesary columns that had a relationship. This naturally increased the file size as I had to duplicate every relationship column in my query, but it also meant that as DAX generally operates based on Column name, it runs after the Query is loaded. If I then set my relationship using this DAX column, then the relationship is maintained.
I got this workaround in trial and error, but to be honest I'm very curious about the metadata and the process that goes around relationships. Right now I have deduced that Query has some way to detect name changes, which is good if you accidently change a name in Query as the relationship will continue to work, but terrible if u intentionally want the relationship to refer to a new column by changing the column name.