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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
NSBS
Helper I
Helper I

Distinct Row Count with Condition

Hi,

 

Kindly help to advise the M language code to count distinct number of country per continent as per below table
Note: all rows should be remained the same

Country ListContinentNumber of Distinct Country Per Continent (expected result)
ChinaAsia2
IndiaAsia2
EnglandEurope3
FranceEurope3
GermanyEurope3
FranceEurope3
USANorth America1
EgyptAfrica1
IndiaAsia2
USANorth America1
1 ACCEPTED SOLUTION
PC2790
Community Champion
Community Champion

Here is the Power Query code to do it:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7IzEtU0lFyLM5MVIrViVbyzEvJRBFwzUvPScxLAQq5lhblF6SCBd2KEvOSU1HF3FOLchPzKgkrDA12BAr45ReVZCg45qYWZSZDbUqvLCgBWZ0GF8JwDVa9sQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Country List" = _t, Continent = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Country List", type text}, {"Continent", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Continent"}, {{"Count", each Table.RowCount(Table.Distinct(_)), Int64.Type}, {"All", each _, type table [Country List=nullable text, Continent=nullable text]}}),
    #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"Country List"}, {"All.Country List"})
in
    #"Expanded All"

I have simply used the concept of Grouping here.

Grouping parameters look like:

PC2790_0-1649822058749.png

The result looks like:

PC2790_1-1649822088592.png

I hope this is what you are expecting.

 

 

View solution in original post

2 REPLIES 2
PC2790
Community Champion
Community Champion

Here is the Power Query code to do it:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7IzEtU0lFyLM5MVIrViVbyzEvJRBFwzUvPScxLAQq5lhblF6SCBd2KEvOSU1HF3FOLchPzKgkrDA12BAr45ReVZCg45qYWZSZDbUqvLCgBWZ0GF8JwDVa9sQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Country List" = _t, Continent = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Country List", type text}, {"Continent", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Continent"}, {{"Count", each Table.RowCount(Table.Distinct(_)), Int64.Type}, {"All", each _, type table [Country List=nullable text, Continent=nullable text]}}),
    #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"Country List"}, {"All.Country List"})
in
    #"Expanded All"

I have simply used the concept of Grouping here.

Grouping parameters look like:

PC2790_0-1649822058749.png

The result looks like:

PC2790_1-1649822088592.png

I hope this is what you are expecting.

 

 

amitchandak
Super User
Super User

@NSBS , refer how to get subtotal in power query

https://www.youtube.com/watch?v=ad7HIQTs_t8

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.