cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
walnei
Helper III
Helper III

Countif POWER QUERY


Hello, could you help me.
I need to do in Power Query, a column with the total number of times a record appears in the key column. I need it to be in the power query and not in the dax.
It would be like a countif in excel

CONT.SE.png

1 ACCEPTED SOLUTION
ChrisMendoza
Super User
Super User

@walnei,

 

This works in my sample, maybe it will work for you?

 

RAND(1,10)
7
2
1
2
9
5
7
8
8
2
3
1
6
2
5
6
5
3
4
3
10
3
10
6
7
4
9
1
5
6

 

let
    Source = Excel.Workbook(File.Contents("C:\Users\username\Desktop\Book99.xlsx"), null, true),
    Tbl_count_Table = Source{[Item="Tbl_count",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Tbl_count_Table,{{"RAND(1,10)", Int64.Type}}),
    
    // Group 'All Rows' in the UI interface generates #"Grouped Rows" see image below
    #"Grouped Rows" = Table.Group(#"Changed Type", {"RAND(1,10)"}, {{"GroupTables", each _, type table}}),
    
    // Counts the rows in the table from previous step
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "CountRows", each Table.RowCount([GroupTables]))
in
    #"Added Custom"

2.PNG

 

Forgot to add the result: You can just remove the column you don't need.

3.PNG






Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



View solution in original post

3 REPLIES 3
ChrisMendoza
Super User
Super User

@walnei,

 

This works in my sample, maybe it will work for you?

 

RAND(1,10)
7
2
1
2
9
5
7
8
8
2
3
1
6
2
5
6
5
3
4
3
10
3
10
6
7
4
9
1
5
6

 

let
    Source = Excel.Workbook(File.Contents("C:\Users\username\Desktop\Book99.xlsx"), null, true),
    Tbl_count_Table = Source{[Item="Tbl_count",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Tbl_count_Table,{{"RAND(1,10)", Int64.Type}}),
    
    // Group 'All Rows' in the UI interface generates #"Grouped Rows" see image below
    #"Grouped Rows" = Table.Group(#"Changed Type", {"RAND(1,10)"}, {{"GroupTables", each _, type table}}),
    
    // Counts the rows in the table from previous step
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "CountRows", each Table.RowCount([GroupTables]))
in
    #"Added Custom"

2.PNG

 

Forgot to add the result: You can just remove the column you don't need.

3.PNG






Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



View solution in original post

@ChrisMendoza

 

Thank you. It worked for me. This way works great. tks for attention

Greg_Deckler
Super User
Super User

Hmm...invoking @ImkeF


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.