cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jtsmithers
New Member

Relationships deleted on refresh

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?

9 REPLIES 9
augusto_flytour
Regular Visitor

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. 

ashishrj
Power Participant
Power Participant

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

 

Options.png

 

Uncheck option as shown below

 

Update_Relationship.png

 

Hope this helps !

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.

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
Resident Rockstar
Resident Rockstar

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.

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

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.

Will do - thanks.

Helpful resources

Announcements
collabdays lisbon

CollabDays Lisbon - 26 November 2022

Sessions include practical, hands-on experience that will help you take the next step in your career and know-how.

Difinity Conference

Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.