Count non empty rows

[Merleau]

Hello,

I have more than 30 columns in my "table" report, some with text value. The table has more than 600 rows. I would like to show for each column, the number of non-blank row. The result should be similar to “NonBlank” in the figure below.

ID	XY	Date	YZ	YT	TT	Address	Count	Phone_nber
1	XY	5/5/1990		YT	TT	12 court rd	3	4678*
2	XY		YZ	YT			3	377*
3		6/6/1966		YT	TT	15 pavillon rd	2
NonBlank	2	2	1	3	2	2	3	2

Using option "Count" just replaces the text with "1" but I would like the text to remain as is "Don't Summarize".

Also

Count_nonblanks = CALCULATE(COUNT(My_Table[ID]), My_Table [column_name]<> BLANK())

Will compute one column at a time, not possible with more than 30 columns.

Can somebody pls help?

Thanks

[dax]

Hi @Merleau , 

You could try below M code to see whether it work or not.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTq0ICISSJnqm+obWloaAJlAFBkCJEJAhKGRQnJ+aVGJQlEKkGcMxCZm5hZaSrE60UpGCO0gPVEwjRAEUmtsbg5RagwRM9M3A9piZoZhi6lCQWJZZk5Ofh7EIpDRSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, XY = _t, Date = _t, YZ = _t, YT = _t, TT = _t, Address = _t, Count = _t, Phone_nber = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"XY", type text}, {"Date", type date}, {"YZ", type text}, {"YT", type text}, {"TT", type text}, {"Address", type text}, {"Count", Int64.Type}, {"Phone_nber", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ID"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each [Value] <> null and [Value] <> ""),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Attribute"}, {{"Count NO", each Table.RowCount(_), type number}, {"all", each _, type table [ID=number, Attribute=text, Value=anynonnull]}}),
    #"Expanded all" = Table.ExpandTableColumn(#"Grouped Rows", "all", {"ID", "Value"}, {"ID", "Value"})
in
    #"Expanded all"

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Merleau]

Hi @dax 

I appreciate your contribution. Could you pls explain how I can pivot the result back for  me to create a table in my report out of that dimension? I tried different scenarios but couldn't get a satisfactory solution.

My goal is to create a table with the 'count no blank" per column. That table would ultimately be downloaded in Excel for further formatting.

Thank you,

Merleau.