CODEWITHSUNDEEP
filterpowerbipowerqueryremove-if
rlsrls
rlsrls

Reputation: 69

How to remove entire rows depending if certain columns have NULL or not - Power BI Query

I have an unpivoted table in the Power BI Query with +20 columns and +10000 rows. The first columns are related to KPI name, month, and other data of interest. The columns after are the columns that contain the actual values I want to display.

Some rows have the KPI description (name, month, etc.) but do NOT any values in the rest of the columns ("NULL").

Is there a way to remove these rows? Remove whenever all the values of the rest of the columns are "NULL". (First columns will never be empty)

enter image description here

Thank you!! I'm still a beginner in this Power BI world :)

Upvotes: 1

Views: 10627

Answers (1)

horseyride
horseyride

Reputation: 21428

If the other columns are always nulls together, then just pick one of those columns and use the arrow atop the column to remove the tick mark from [ ] null and filter it out

= Table.SelectRows(#"PriorStepName", each (Total number of shipments inbound] <> null)

If you have to check that every column except certain columns have nulls, then you could do this, which counts the number of nulls on each row. Then filter that column for proper number of nulls using the drop down atop that new column

= Table.AddColumn(#"PriorStepName", "Count", each  List.NonNullCount(Record.ToList(_)))

Similarly you could count the number of null columns excluding the first X columns (here the first 2 columns) then filter that column

= Table.AddColumn(#"PriorStepName", "Count", each List.NonNullCount(List.Skip(Record.ToList(_),2)))

Upvotes: 3

Related Questions