Merge Queries or Use Relationships

[alexlibby]

Good afternoon,

I have a question about when to use Relationships vs Merge Queries in Power Query. I have two large files. There are about 23,333 rows and tons of columns in each. One file is Historic and the other is current and they each have a unique identifier (constituent ID). I've read that you should condense your data as much as possible, so I thought I would use relationships to report the two related queries. But, just watched a video saying merge queries are the way to go. Does anyone have any guidance for me?

[Migasuke · Accepted solution]

Hi,

unfortunatelly there is not a one single solution . Main goal for merging the tables is to keep you model simple and easier to understand.
Imagine you have 30 tables in your model and you create relationship for all of those - it will become very messy. It is better to do maybe some merging and instead of having 30- you might lower it for 15(depends on data) and suddenly is much easier to understand what is going there.

If you know your model will be easy to understand even without some Power Query transformation, you can just keep the relationships with Dimensions and Fact tables - but in most cases you will combine both approaches.

[Fowmy · Accepted solution]

As per your explanation, I would suggest you try to minimize the number of columns in both the tables considering the reporting requirements. It is always better to keep your fact table "Thin and Long", if required you may UnPivot the columns. In this way, the relationship will be effective. Merging the table increases the columns which may hinder the performance and size.

Modeling Tips: https://powerbi.microsoft.com/en-us/blog/best-practice-rules-to-improve-your-models-performance/

[Migasuke · Accepted solution]

Hi,

unfortunatelly there is not a one single solution . Main goal for merging the tables is to keep you model simple and easier to understand.
Imagine you have 30 tables in your model and you create relationship for all of those - it will become very messy. It is better to do maybe some merging and instead of having 30- you might lower it for 15(depends on data) and suddenly is much easier to understand what is going there.

If you know your model will be easy to understand even without some Power Query transformation, you can just keep the relationships with Dimensions and Fact tables - but in most cases you will combine both approaches.