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.
Hello House,
I have a dataset where I used "SplitColumn by Delimeter" to split a particular column into several columns. Now, I will like to have a calculated column that will count the number of cells with non-blank for each row.
Thank you.
@Anonymous I would recommend unpivoting those columns in Power Query. However, if for some reason you need them in multiple columns, you could use MC Aggregations:
MC Count Blank =
VAR tmpCol1 = SELECTCOLUMNS(Data,"Column",[Column1])
VAR tmpCol2 = SELECTCOLUMNS(Data,"Column",[Column2])
VAR tmpCol3 = SELECTCOLUMNS(Data,"Column",[Column3])
VAR tmpCol4 = SELECTCOLUMNS(Data,"Column",[Column4])
VAR tmpTable = UNION(UNION(UNION(tmpCol1,tmpCol2),tmpCol3),tmpCol4)
VAR tmpValue = COUNTROWS(FILTER(tmpTable,ISBLANK([Column]))
RETURN tmpValue
Thank you very much for your quick response. This is appreciated.
I actually do need the splitting in the multiple columns hence this request.
When I copied and edited the syntax you suggested, it gave me some error. Please see the screenshot below
I think the error is from second to last step of the formula (ISBLANK([Column]))):
@Anonymous There is just a missing ) in the line before RETURN:
MC Count Blank =
VAR tmpCol1 = SELECTCOLUMNS(Data,"Column",[Column1])
VAR tmpCol2 = SELECTCOLUMNS(Data,"Column",[Column2])
VAR tmpCol3 = SELECTCOLUMNS(Data,"Column",[Column3])
VAR tmpCol4 = SELECTCOLUMNS(Data,"Column",[Column4])
VAR tmpTable = UNION(UNION(UNION(tmpCol1,tmpCol2),tmpCol3),tmpCol4)
VAR tmpValue = COUNTROWS(FILTER(tmpTable,ISBLANK([Column])))
RETURN tmpValue
Thank you @Greg_Deckler . The syntax returned aggregate value for all rows. This is not what I want. Please see screenshot below:
For this screenshot, you would notice that only the first column has values while the rest has are blanks. The figures here should 1. Please can you help out.
@Anonymous Maybe:
MC Count Blank =
VAR __Table = SUMMARIZE('Table',[Column],"__Column1",MAX([Column1]),"__Column2",MAX(Column2]),"__Column3",MAX([Column3]),"__Column4",MAX([Column4]))
VAR tmpCol1 = SELECTCOLUMNS(Data,"Column",[__Column1])
VAR tmpCol2 = SELECTCOLUMNS(Data,"Column",[__Column2])
VAR tmpCol3 = SELECTCOLUMNS(Data,"Column",[__Column3])
VAR tmpCol4 = SELECTCOLUMNS(Data,"Column",[__Column4])
VAR tmpTable = UNION(UNION(UNION(tmpCol1,tmpCol2),tmpCol3),tmpCol4)
VAR tmpValue = COUNTROWS(FILTER(tmpTable,ISBLANK([Column])))
RETURN tmpValue
@Anonymous I'm on my phone so hard to see error message. But may try
COUNTROWS(FILTER(tmpTable,[Column]=BLANK()))
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.
User | Count |
---|---|
39 | |
20 | |
19 | |
16 | |
15 |
User | Count |
---|---|
47 | |
28 | |
25 | |
17 | |
17 |