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,
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
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.
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.
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 |
---|---|
110 | |
96 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |