Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
Applicable88
Impactful Individual
Impactful Individual

Grouping a orginal table with two count aggregation

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 CountID 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. 

1 ACCEPTED SOLUTION
v-stephen-msft
Community Support
Community Support

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}})

vstephenmsft_0-1649130058732.png

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.

View solution in original post

6 REPLIES 6
v-stephen-msft
Community Support
Community Support

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}})

vstephenmsft_0-1649130058732.png

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. 

ronrsnfld
Super User
Super User

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).

Anonymous
Not applicable

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?

Anonymous
Not applicable

@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

 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.