cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

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

2 REPLIES 2
Highlighted
Community Support
Community Support

Re: Count non empty rows

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.

Highlighted
Helper I
Helper I

Re: Count non empty rows

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.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors