Forum Discussion
check for relation with other table
Community ChampionIf there is a relationship between those two tables and you just want to identify the number of records related per key, then you can simply drag the Primary Key field from Table1 and any field from Table2 on to a table visual. By default, it sums up the field (if it is numeric) from table2 then you can change it to "Count" under values section of the visual.
Hi PattemManohar, there is a relationship between the tables but not for all records in table 1 (FeitLeveringActueel) there is actually a (or more) row(s) in table 2 (FeitFactuurregel).
- PattemManohar
Yes, if there is no matching record found in the child table then you can't see the parent key in the counts.
To handle this, I've tried with below steps:
1. Merged both Parent and Child tables into a new table (Left Outer Join -- Parent on Left), where you can see the NULL values for non-matching in child table.
2. Add a conditional column on the child table key field, if it is null 0 else 1, I call it is as "Check" (Make sure it is numeric data type)
3. Use this new table which contains both parent and child fields along with the new field added in above step (make sure you can expanded the fields after merging). Drag ParentKey filed and Check field (change the value type to "SUM")
Then it will show the count as 0 for non-existance keys in child table.
Hi PattemManohar, I'm a newbie so most certainly I'm doing something wrong, but for now I don't have the 'Create table' available. May it's because I',m working in the DirectQuery-modus? Is there a(nother) way to get this option available?
- PattemManohar
Under Power Query Editor, you can see the Merge Queries option... as below