Understanding power query in detail for data cleaning operation

Understanding Cardinality:

In our data, we have a relationship between country table and sample table using the country field. The country table in the first model have unique values for the respective fields. However, sample table have each value repeating for multiple times by date, country, segment, etc.

Similarly project name in the project info have unique values but has multiple values in project detail having many employees working in a same project.

So joining field contains unique values in at least 1 of the tables. It allows you to create one to many or many to one relation.

Many to many relation:

Many to many is useful in complex models but in order to to use this relation we have to understand it’s downside.

Comparing One to many relation:

Applying single directional filter aggregates the contribution field of employees.

Many to one:

The cross filter direction gives a choice between either single or both directions. Relationship flows from the table with unique values to the table with many values.

The single direction is recommended for most cases but here bi-directional relationships allows the data flow in both direction. However, bidirectional decreases performance in the model and also introduce some ambiguity in the data.