Unable to FULL JOIN or Choose Direction of Cardinality

[captainlaw]

Hello,

Couple of questions when I try to join two excel files in the Desktop.

1. Is it possible to do FULL JOIN?

2. When I switch from Many-to-1 to 1-to-Many, Desktop provides a message that's invalid?   I try to flip the table in Edit Relationship, and Desktop would automatically flip it back. Why can't I manually select which direction the join (RIGHT OR LEFT) ought to be? 

Your help is much appreciated.

[KHorseman]

When you say 1-to-Many and Many-to-One it sounds like you're talking about creating a relationship. That is not at all the same thing as joining tables. Which do you want, to do a join or create a relationship?

[captainlaw]

To answer your question KHorseman, join and relationship should accomplish the same thing.  Merge that Ankit suggested would eliminate the need to have relationship.  JOIN is typically the way to create relationship between data sources.

[KHorseman]

@captainlawthey may accomplish the same thing in a SQL query but within the functionality of a BI report they are not at all the same. The two activities aren't even performed in the same editor window. There is a big difference between writing a measure that refers to two separate tables across a relationship and a measure that refers to two columns in the same table, and there is a big difference between merging queries in the query editor and creating relationships in the data model. Depending on whether you want a join resulting in a single table or you want two tables with a relationship between them, the steps you would need to take in Power BI are vastly different. We need to know which question you want answered. That's why I asked.

[captainlaw]

Since merge join worked after workaround with data aggregation, how can FULL JOIN be accomplied in relationship?

[ankitpatira]

@captainlaw

1. You can achieve FULL JOIN by going to query editor, select Merge Queries under Combine on Home tab and selecting Full Outer as Join Kind.

2. One to many and many to one relationship works on data. All values in your PK table should exist in FK table and vice versa. Make sure there are no missing values in PK table (ie values in FK table but doesn't exist in PK table) and it should work regardless of the direction.

[captainlaw]

Thank you Ankit.  I was able to get Merge started, but one issue I see is missing records when I include records that are null in the dashboard.  Null records (customerid) still have sales dollars associated with them.  Is there a way to force the dashboard to display all records even if some of the attribute columns have null data?

Thanks.

[captainlaw]

Interesting.  After I switch aggregation to "Do Not Summarize" then the dashboard shows correctly what the query was showing.

Issue resolved.

Below screenshot was the weird phenomenon that I was seeing (left is Dashboard, right is the query).

 

[ankitpatira]

@captainlaw glad you got it working using Merge and full join.