Forum Discussion
Solved
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 ...
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"Forgot to add the result: You can just remove the column you don't need.
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"
Forgot to add the result: You can just remove the column you don't need.
