Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 🙂
Solved! Go to 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.
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.
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))
@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 , 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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.