Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
i am trying to group by in power query but i only want it to count distinct on one column.
this is the current m code
#"Grouped Rows" = Table.Group(Source, {"PLOT", "BLOCK", "UNIT TYPE", "APARTMENT"}, {{"Count", each Table.RowCount(Table.Distinct(_)), type number}}),
how would i adjust it to do a distinct count on apartment?
Proud to be a Super User!
Solved! Go to Solution.
In Group By - Advanced - you can add an aggregation and choose All Rows to get all data.
That will give you a column with nested tables you can expand.
list.count on list.distinct should do the trick
{{"number_of_flows", each List.Count(List.Distinct([APARTMENT])), Int64.Type}
Thanks for saving my time, I've applied this to my report and it work!!
There is no solution using visual
= Table.Group(#"Expanded chat messages", {"keyword_category"}, {{"messages", each List.Count(List.Distinct([id])), Int64.Type}, {"users", each List.Count(List.Distinct([user_id])), Int64.Type}})
Don't adjust the code but start over. First select only the "APARTMENT" column and then choose Group By.
ok but will that output all the other columns too? i need the count distinct of the apartments but by all those columns... sorry if i am not getting it.
Proud to be a Super User!
In Group By - Advanced - you can add an aggregation and choose All Rows to get all data.
That will give you a column with nested tables you can expand.
Wow, this has just simplified so many of my queries! Thanks so much!
Hello,
I believe i have the same, or very similar, requirement and am not getting to the solution, though I think I am very close. To use a simplified example, consider this table (sorted by position):
I would like to use PowerQuery to add a column that shows the DISTINCT COUNT OF SUPERVISORS PER POSITION.
So, the end result would be this:
I can't seem to get @MarcelBeug's solution to work for me.
Here's the example table: Example Data
Any help would be greatly appreciated,
Thank you,
- Kurt
Did you get the solution to your problem?
I never got it to work as intended this solution, i just ended up grouping by again, but i have to say ive been using this trick alot for other group by's and its great so thank you @MarcelBeug !
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
93 | |
75 | |
62 | |
50 |
User | Count |
---|---|
147 | |
107 | |
105 | |
87 | |
61 |