Forum Discussion
Visualizing Data with Null Records AKA Left Join
I notice when you have a table and you bring in a field, say "customer". It results in a list of rows. Lets say 25 rows for this example. Next you want to bring in another field. For this example lets say "sales". Now that you have brought in the records it now shows only 20 because 5 customers do not any record for sales. Is there a way to show the 25 customers with the 20 sales records. A similar example would be a left and inner join between 2 tables. In a left join, the data would show 25 customers with 20 sales records and 5 null records, whereas the inner join would show the 20 customers that have the 20 sales records. Is there a way to view the data in Power BI as a "Left Join"
6 Replies
Hi Anonymous
Import your two tables, go to Power Query Editor, merge table as new, specify the 2 keys, and select LEFT OUTER JOIN. It will left join the two tables.
How do you do it, if you only have one table that holds the data.
I start with 36 rows and end with 24 after I bring in the measure. I can't really modify the join because the data is held in the same table. I am connecting to Dynamics 365 (Online).
Anonymous
How is your table pulled? Can you share your query?
Also the screenshot you sent is blurred, I can't see anything.
Hello,
I would recommend you look at the model. Is the relationship between the two tables a many to many? This would indicate there are "duplicates" in both files. Note, blanks are consider duplicates. This may cause the issue you are seeing. I had to create a new variable such that the "right" table that I wanted to join (left join) to the base table had unique values in the "key/variable" that was used to merge. Then I reconnected the data so that it is a "one to many" connection. I believe a "one-to-one" connection would also work. Now the visual sums properly.
