Hi
Ive been trying to left join tables (like you would in SQL) where any rows that dont match (in the right table) return nulls.
Ive come up with the follwoing work around but it seems very long winded. Perhaps I should create the view in SQL instead?
Any thoughts anyone?
Scenario:
I have two tables with data…
Animals Table:
AnimalID | Animal | Age | Type | CreatedBy |
1 | Dog | 5 | Pet | me |
2 | Cat | 4 | Pet | me |
3 | Rat | 1 | Wild | me |
4 | Mouse | 24 | Wild | me |
5 | Rabbit | 46 | Pet | me |
6 | Hen | 3 | Wild | me |
7 | Horse | 5 | Wild | me |
Notes Table:
AnimalID | Notes | CreatedBy |
1 | aaa | me |
1 | bbb | me |
2 | ccc | me |
3 | fff | me |
4 | ggg | me |
5 | hhh | me |
6 | iii | me |
6 | jjj | me |
6 | kkk | me |
I want to join these two tables so that the notes are included in the “Animals” table. This would mean two rows for AnimalID 1 (as there are two notes for it) and three rows for AnimalID 6 (as there are three notes for it). In addition, AnimalID 7 would be null for the notes (as there isn’t one) IT WOULD STILL BE IN THE CHART BUT RETURN NULL FOR THE NOTES.
PowerBI:
So I bring the tables into Power BI.
Power BI has automatically detected a join between the two IDs and applied it correctly stipulating that there are many notes to one Animal.
What I can’t do here is stipulate the type of join. It is not an option in the “Properties” of the join.
So I create a very simple chart to display the Animals and their notes:
I get a chart as expected but Animal 7 is not included.
So PowerBI is doing an inner join on the two tables by default. I need a left join.
So I use a DAX formula to create a NEW table which will be the combination of the tables above (using a left join).
Steps:
Modelling tab, New Table.
DAX Formula is “NewJoinTable = NATURALLEFTOUTERJOIN(Animal, Notes)”
I get an error saying that the columns are already used in the other table.
The problem is that the same column name is used in both tables. Fields "AnimalID" and “CreatedBy”.
So I will change the names in one of the tables:
The new table can then be created:
And I get the results with the null AnimalID 7.
