Merging tables

[yusif1999]

Hello all,

I have prepared the powerbi file where I get data from SharePoint and SQL server.

Tables have been merged based on 3 different columns. When I merge them and then "close and run" I see that relationship have been done based on a different single column.

The solution I found out, was combing these 3 columns in power query to one in both of the tables and merging tables based on that column.

Even when doing In that way, I have to manually edit the relationship(as by default it takes another column in the "model" tab)

Do you have any other idea? 

[edhans]

When merging based on multiple columns in Power Query, you need to press CTRL-CLICK on the 2nd-Nth fields, and you have to do it in the second table in the exact same order so Power Query will use the right merge keys.

The tiny 1/2/3/n numbers show up in the column names so you can tell which goes with which

You are also using the term relationships. You cannot merge in the Power BI datamodel. Those are filter relationships and only one can be active at a time, all others are inactive. You can change which is active using the USERELATIONSHIPS modifier in CALCULATE, but you can still only do one at a time. To do more, you have to use the TREATAS function, which gets a bit more complex.

Merging is best done in Power Query - that is what it is designed for.

 

[yusif1999]

Thank you very much for the detailed message.

I have merged tables exactly how you did (via powerquery). 

The point that confuses me is a completely different relationship in a data model which is generated automatically. It is selected randomly. When I run the model it raises errors because of the duplicates based on the "filter relationship"

These duplicates are from the column which is in the "data model (filter relationship)".

Is there any chance of avoiding this error?

Thanks in advance for reply!

[edhans · Accepted solution]

Disable automatic relationship creation. Worst setting after auto date/time intelligence.

Create the relationships you need manually, 100% of the time. Don't let the product's AI try to figure your model out. It fails enough times to cause massive irritation.

[yusif1999]

Thank you very much for the assistance!