1

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 :)

rlsrls
  • 69
  • 1
  • 4

1 Answers1

2

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)))
horseyride
  • 17,007
  • 2
  • 11
  • 22
  • Thank you! The last two are definitely what I needed. Now I have encountered another issue: it does not count all the rows I want (it is somehow limited to 10 rows). It does count NULL... But partially – rlsrls Jun 09 '22 at 16:42
  • Not sure how you can partially count nulls ... it either counts them or not. This would work on all rows. If your code is not doing that, then you have created a filter or somehow limited the data you are working with. Maybe create another question and post your code – horseyride Jun 09 '22 at 17:10