Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hello,
I tried to use the "Group by" function within PowerQuery, but got two same row counts. So I guess it count all rows, even though I chose "not the empty fields".
My example original table:
Categories | Year | ID SCAN | ID MAN | Remarks | Info | |||||
A | 2019 | 123 | ||||||||
A | 2019 | 321 | ||||||||
A | 2019 | 555 | ||||||||
A | 2020 | 3132 | ||||||||
B | 2019 | 231 | ||||||||
B | 2022 | 777 | ||||||||
B | 2022 | 543 | ||||||||
B | 2022 | 888 |
I want to group the categories and the year together, and have a aggregation how much count of ID Scan or ID MANUAL I got.
That should be the expected result:
Categories | Year | ID SCAN Count | ID MAN Count | |||
A | 2019 | 2 | 1 | |||
A | 2020 | 1 | 0 | |||
B | 2019 | 1 | 0 | |||
B | 2022 | 0 | 3 |
Thank you very much in advance.
Best.
Solved! Go to Solution.
Hi @Applicable88 ,
Try to use List.NonNullCount function.
= Table.Group(#"Changed Type", {"Categories", "Year "}, {{"ID SCAN Count", each List.NonNullCount([ID SCAN]), Int64.Type},{"ID MAN Count",each List.NonNullCount([ID MAN]), Int64.Type}})
The whole M languae:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wck4sSU3PL8pMLVbSUYpMTSxSANKeLgrBzo5+EJYvmBGUmptYlA1S5JmXlq8UqxOt5AjkGBkYWgIpQyNjIKkAx2jSIEFjI0Nc0qampjh0GxmAdBoaG2HIOyG0Gxkb4pA2gukzNzfHJ21qYoxP2sLCAiEdCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).2" = _t, #"(blank).4" = _t, #"(blank).6" = _t, #"(blank).8" = _t, #"(blank).10" = _t]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Categories", type text}, {"Year ", Int64.Type}, {"ID SCAN", Int64.Type}, {"ID MAN", Int64.Type}, {"Remarks", type text}, {"Info", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Categories", "Year "}, {{"ID SCAN Count", each List.NonNullCount([ID SCAN]), Int64.Type},{"ID MAN Count",each List.NonNullCount([ID MAN]), Int64.Type}})
in
#"Grouped Rows"
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Applicable88 ,
Try to use List.NonNullCount function.
= Table.Group(#"Changed Type", {"Categories", "Year "}, {{"ID SCAN Count", each List.NonNullCount([ID SCAN]), Int64.Type},{"ID MAN Count",each List.NonNullCount([ID MAN]), Int64.Type}})
The whole M languae:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wck4sSU3PL8pMLVbSUYpMTSxSANKeLgrBzo5+EJYvmBGUmptYlA1S5JmXlq8UqxOt5AjkGBkYWgIpQyNjIKkAx2jSIEFjI0Nc0qampjh0GxmAdBoaG2HIOyG0Gxkb4pA2gukzNzfHJ21qYoxP2sLCAiEdCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).2" = _t, #"(blank).4" = _t, #"(blank).6" = _t, #"(blank).8" = _t, #"(blank).10" = _t]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Categories", type text}, {"Year ", Int64.Type}, {"ID SCAN", Int64.Type}, {"ID MAN", Int64.Type}, {"Remarks", type text}, {"Info", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Categories", "Year "}, {{"ID SCAN Count", each List.NonNullCount([ID SCAN]), Int64.Type},{"ID MAN Count",each List.NonNullCount([ID MAN]), Int64.Type}})
in
#"Grouped Rows"
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@v-stephen-msft thank you for providing the pbix. I see that you also didn't use the "group by" button, instead you directly wrote the table.goup function. Through your example I saw my mistake and why I got the same row counts, because I had it as string and not integer, so the funtion doesn't know how to ignore null values.
Thank you so much.
Best.
Maybe a language problem? I do not see an option for Count "not the empty rows" for the Table.Group function. What is the M Code that is generated (see the Advanced Editor or the contents of the formula bar when selecting that step).
let
Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUVIAYiMDQ0so09DIGMrCxLE6WLXAsLGRIbFaTE1NibTFyABmuqGxEV49TpjWGBljugi7FiN0s83NzUnVYmqCGXQEtFhYWGBqiQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Categories = _t, #" " = _t, Year = _t, #" .1" = _t, #"ID SCAN" = _t, #" .2" = _t, #"ID MAN" = _t, #" .3" = _t, Remarks = _t, #" .4" = _t, Info = _t]),
#"Modificato tipo" = Table.TransformColumnTypes(Origine,{{"Categories", type text}, {" ", type text}, {"Year", Int64.Type}, {" .1", type text}, {"ID SCAN", Int64.Type}, {" .2", type text}, {"ID MAN", Int64.Type}, {" .3", type text}, {"Remarks", type text}, {" .4", type text}, {"Info", type text}}),
#"Rimosse colonne" = Table.RemoveColumns(#"Modificato tipo",{" ", " .1", " .2", " .3", "Remarks", " .4", "Info"}),
#"Raggruppate righe" = Table.Group(#"Rimosse colonne", {"Categories","Year"}, {{"id scan count", each List.Count(List.RemoveNulls(_[ID SCAN])), Int64.Type},{"id man count", each List.Count(List.RemoveNulls(_[ID MAN])), Int64.Type}})
in
#"Raggruppate righe"
@Anonymous thanks for the quick reply. I'm not fully getting all steps. Are the blank fields the problem? But I already chosed "count only not empty fields" in the Group by options, why I need to remove nulls?
@Applicable88 It is not clear if what I have proposed works but it is not clear or if it does not work and it is not clear why it does not work.
I can't comment on what you did unless you show your code.
PS
certainly instead of the two List functions .... I could have used this one that I didn't remember when I wrote the code.
List.NonNullCount(list as list) as number
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
24 | |
12 | |
11 | |
11 | |
8 |
User | Count |
---|---|
43 | |
25 | |
16 | |
14 | |
12 |