Relationships deleted on refresh

[jtsmithers]

Hi guys,

I'm having a problem where all of my relationships to a particular table are being deleted each time I refresh my data (just these relationships - the rest are still fine). I have turned off "Autodetect new relationships after data is loaded" and am pretty sure that there is no ambiguity in the relationships.

Any ideas on what this could be?

[augusto_flytour]

Has anyone got the solution? I have this same issue now but I can't find any information that could help.

Tks!

[Anonymous]

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. 

[ashishrj]

@jtsmithers Can you try unchecking "Update relationships when refreshing queries " option and check as shown below !

Uncheck option as shown below

Hope this helps !

[jtsmithers]

Hi @ashishrj. Thanks - I have already got that unchecked but am still seeing the issue.

I still don't know what caused the issue, but seem have circumvented the issue by creating a number index in all tables involved and using this to create the relationships, rather than the text field I was previously using.

[YankySvelte]

I'm experiencing the same issue, i just updated to Power BI July 2017, and every once in a while all of my hidden fields become visible and several of my relationships become deleted.

still looking for help...

[greggyb]

Can you recreate the relationships between those tables after your data refresh has completed?

Does your refresh invalidate the relationship by loading duplicate keys into both tables? Power Pivot only natively supports 1:1 and 1:N relationships, but not N:N.

[jtsmithers]

I can re-create the relationships. And the dimTable always has unique values.. What else could it be?

[greggyb]

I only had one idea on this. Make sure to send a frown through the File menu feedback options. That is the official medium for bug reports. Include reproduction steps. If you can, also include a sample dataset that recreates the issue.

The forums are great to get feedback and thoughts from others, but are not the appropriate avenue to submit a bug report. Hopefully someone else can come along and help identify the root cause or a workaround, but the devs will respond more readily to a formal bug submission.

[jtsmithers]

Will do - thanks.