Forum Discussion

NSBS's avatar
NSBS
Helper I
4 years ago
Solved

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
Need Help
Tutorial Requests
  • PC2790's avatar
    PC2790
    4 years ago

    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:

    The result looks like:

    I hope this is what you are expecting.

     

     

2 Replies

  • PC2790's avatar
    PC2790
    Community Champion
    4 years ago

    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:

    The result looks like:

    I hope this is what you are expecting.