Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
vanessafvg
Super User
Super User

count distinct on column in power query

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?





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




1 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.

 

Group By.png

Specializing in Power Query Formula Language (M)

View solution in original post

11 REPLIES 11
Anonymous
Not applicable

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

MarcelBeug
Community Champion
Community Champion

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

Specializing in Power Query Formula Language (M)

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.





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

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.

 

Group By.png

Specializing in Power Query Formula Language (M)

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

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

kbarber
Frequent Visitor

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

 

simpletable.png

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:

 

simpletable2.png

 

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
Frequent Visitor

Did you get the solution to your problem?

kbarber
Frequent Visitor

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 !





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors