Forum Discussion

nikhil425's avatar
nikhil425
Helper I
7 years ago

Dynamic Field Counts Without Blanks (Table)

Hello there,    Can any one please help me! I have a table in Power BI with more than 100 columns/fields. Table gets refreshed ofter with SSAS Tabular live connection.   The requirement is to cou...
Need Help
nikhil425's avatar
nikhil425
Helper I
7 years ago

Source Data is a just table with rows and columns, From the sample screen shot below, I have to count all the values except Blanks. 

 

 

TeigeGao's avatar
TeigeGao
Solution Sage
7 years ago

Hi nikhil425 ,

We can do it in Power Query. Please refer to the following steps:

First we can create a function in Power Query like below:

let 
  countif = (table_content as table, column_name as text) as number =>
    let
      count_rows = Table.RowCount(Table.SelectRows ( table_content, each Record.Field(_,column_name) <> null))
    in
      count_rows
in
    countif

The above function will count not null rows for a column, then we can use the function for each column using the following M query:

let
    Source = ***,
    #"Changed Type" = ***,
    Header = Table.ColumnNames(Source),
    Totals = Table.FromRows({List.Transform(Header, each Query1(#"Changed Type",_))}, Header)
in
    Totals

The result will like below:

Please refer to the following pbix file: https://1drv.ms/u/s!Ao9Of0JgO6MU72UvnGI7ONURDYSH

Best Regards,

Teige