Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
yusif1999
Helper I
Helper I

Merging tables

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? 

 

 

1 ACCEPTED SOLUTION

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

edhans_0-1657811801343.png

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

4 REPLIES 4
edhans
Super User
Super User

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

 

Merge.gif

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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!

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

edhans_0-1657811801343.png

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Thank you very much for the assistance!

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors