Dynamic Field Counts Without Blanks (Table)

[nikhil425]

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 count the Rows of each field (without Blanks). If it is just few fields, I can write DAX for each field to count the Non Blank rows. However its across whole table. Is there a way to do it using DAX?

Above screen shot is the output I would like to show.

Thanking you,

Boez   

[Greg_Deckler]

What does your source data look like?Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[nikhil425]

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]

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