Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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
Solved! Go to Solution.
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.
Proud to be a Super User!
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.
Proud to be a Super User!
Hmm...invoking @ImkeF
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
138 | |
70 | |
65 | |
52 | |
52 |
User | Count |
---|---|
210 | |
92 | |
64 | |
59 | |
56 |