Power BI Query: Identify Parent record with qualifying Child records

[twitchingdog]

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

[artemus]

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
    Custom2

Not sure what this has to do with a direct relationship though

[twitchingdog]

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

[Anonymous]

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

[twitchingdog]

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?