Count blank cells across a range of column..

[Anonymous]

Hi There,

I am new to Power Query and stuck up with below, Below formula from excel counts number of blank cells from a cell range.

I am trying to replicate the same in power bi query editor. My plans are to create calculated fields and use the logic to get the number of blank cells present.

=COUNTIF(Y2:AE2,"")

Thanks in Advance 🙂

[v-frfei-msft · Accepted solution]

Hi @Anonymous ,

Please transpose the table in power query like that.

Close and apply and create a measure as below.

Measure = CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[Column1] = BLANK()))

Pbix as attached.

[artemus]

You can add a new column with a formula like:

List.Count(List.Select(Record.FieldValues([[Y], [Z], [AA], {AB], [AC], [AD], [AE]]), each _ = "" or _ = null))

[Anonymous]

@artemus Thanks a lot for your response...

Below is the further scenarion post using the formula.

The syntax for 'COUNT' IS INCORRECT.
(DAX(LIST.COUNT(List.Select(record.Fieldvalues(column1,col2.....,or _ = " ")

Further to this it says "List.Count/List.Select/Record.fieldvalues is not a function".

Kindly Advise!!

Regards..

[Anonymous]

@amitchandak any advise on this?

[amitchandak]

@Anonymous , the options that I can quickly think off.

Measure =
calculate(count(table[Column1]), isblank(table[Column1]))
+calculate(count(table[Column2]), isblank(table[Column2]))
+calculate(count(table[Column3]), isblank(table[Column3]))

OR
Measure =
var _1, union(all(table[Column1]),all(table[Column2]),all(table[Column3]))
return
countx(_1,[Column1])

s