Forum Discussion
Power BI Query: Identify Parent record with qualifying Child records
Hi All - Looking for some assistance with Query or filter
Issue: I have a child TABLE A with a Direct relationship to PROJECT table. I want to filter TABLE A to ONLY show projects that have NO child records in TABLE A that have a value in Status.
Example:
TABLE A
| ID | ProjectID | Status |
| 1 | A | null |
| 2 | A | null |
| 3 | A | null |
| 4 | B | Yellow |
| 5 | B | n/a |
| 6 | B | Red |
| 7 | C | null |
| 8 | C | null |
| 9 | C | null |
| 10 | D | Green |
| 11 | D | null |
| 12 | D | null |
Expected Results after FILTER:
| ProjectID |
| A |
| C |
Thanks for assistance
MB
4 Replies
let Source = <Your Data>, #"Grouped Rows" = Table.Group(Source, {"SourceNamespace"}, {{"Rows", each _, Value.Type(Source)}}), Custom1 = Table.SelectRows(#"Grouped Rows", each Table.SelectRows([Rows], each [ID} <> null){0}? = null), #"Removed Columns" = Table.RemoveColumns(Custom1,{"ID"}), Custom2 = if Table.RowCount(#"Removed Columns") = 0 then Table.RemoveRows(Source, 0) else Table.ExpandTableColumn(#"Removed Columns", "Rows", Table.ColumnNames(#"Removed Columns"[Rows]{0})) in Custom2Not sure what this has to do with a direct relationship though
thanks for info, I will try this out.
I mention the ProjectID, because ultimately thatis what will join to my Project table to limit the # of child records reported on. ID is just the main table GUID, which is not necessary. It only was showing the respective table structure
I think I figured this out:
1. Added a Custom column with If statement to ADD 0 for nulls and 1 for not null
= Table.AddColumn(#"Removed Other Columns", "Custom", each if [cxp_status] = null then 0 else 1)
2. Added a Group By on ProjectID and SUM of Custom Column added to Count column
= Table.Group(#"Added Custom", {"PROJECTID},{{"Count", each List.Sum([Custom]), type number}})
3. FLITERED on Count = 0
= Table.SelectRows(#"Grouped Rows", each ([PROJECTID] <> null) and ([Count] = 0))
Im left with 2 columns PROJECTID and Count. I can then filter the on Count = 0 which should give me all projects that have all related child records with status = NULL
sound right?
Hi twitchingdog ,
Using Table.Group and List.NonNullCOunt:
#"Grouped Rows" = Table.Group(Source, {"ProjectID"}, {{"Count", (t as table)=>List.NonNullCount(t[Status]), type number}}), #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([Count] = 0))You are interested in rows that have zero NonNullCount.
Kind regards,
JB
