Distinct Row Count with Condition

[NSBS]

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 List	Continent	Number of Distinct Country Per Continent (expected result)
China	Asia	2
India	Asia	2
England	Europe	3
France	Europe	3
Germany	Europe	3
France	Europe	3
USA	North America	1
Egypt	Africa	1
India	Asia	2
USA	North America	1

[PC2790 · Accepted solution]

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.

[amitchandak]

@NSBS , refer how to get subtotal in power query

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