Forum Discussion

Merleau's avatar
Merleau
Helper II
6 years ago

Count non empty rows

 

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

Need Help
Tips and Tricks

2 Replies

  • dax's avatar
    dax
    Community Support
    6 years ago

    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's avatar
      Merleau
      Helper II
      6 years ago

      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.