Creating a Summary from a table with a LOT of columns

[natwilliams]

Hi, 

I have a table with 190 columns and I want to create a summary table with the number of blanks in each columns. 

So the result would be:

Col        | countblanks()

-------------------------

col1      |    0

-------------------------

col1      |    24

-------------------------

...          |    ....

-------------------------

col190  |    33

I have tried =SUMMARIZE( <table>, <groupBy_columnName>[, <groupBy_columnName>]…[, <name>, <expression>]…)  with COUNTBLANK() but i dont want to have to list out all 190 columns. 

Any clue how to quickly generate a new table that contains summarizing values of ALL the table's clolumns without having to explicitly list them?

Thank you!

[MDodds · Accepted solution]

Hello,

There are a couple of ways you could approach this, but I have gone through with a solution following your approach using DAX to form a new table rather than Power Query or other method.

Firstly I created a sample data table like below for this example:

Per this you want to count the blanks for each distinct col. So for "A", there would be 1 blank as an example.

My next step is to create a new table with a distinct list of "Col".

I did this using the following DAX.

Summarise = Distinct('Sample Data'[Col])

Next step is to set the relationship between the new "Summarise" table and the original data table. Join Col on Col, relationship should be 1 to many.

Now we want to count the blanks from the original table. Hit the create a new column button and use the following DAX:

Count Blank = Calculate(Countblank('Sample Data'[Data]))

The end result should be a table like below:

I am hoping this is helpful, I have also uploaded my PBIX file at the link below should you wish to explore my solution further.

https://www.dropbox.com/s/iwtu4etzjlwsz9c/NatWilliams%20-%20Creating%20a%20Summary%20from%20a%20tabl...

Shoot me a message if you need further help!