count distinct on column in power query

[vanessafvg]

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?

[Anonymous]

list.count on list.distinct should do the trick

{{"number_of_flows", each List.Count(List.Distinct([APARTMENT])), Int64.Type}

[MyroslavaM]

if you want to exclude blanks from results
{{"number_of_flows", each List.NonNullCount(List.Distinct([APARTMENT])), Int64.Type}

[Hokhok]

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

[MarcelBeug]

Don't adjust the code but start over. First select only the "APARTMENT" column and then choose Group By.

[vanessafvg]

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.

[MarcelBeug · Accepted 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.

[Sismis]

Wow, this has just simplified so many of my queries! Thanks so much!

[affan]

@MarcelBeug Thanks for the fantastic tip. You've made my day.

[kbarber]

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

[UG14]

Did you get the solution to your problem?

[kbarber]

example file answer 

[vanessafvg]

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 !