Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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
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
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
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.