Latest Common Month in a Country considering Category

[Anonymous]

I am trying to create one custom column(Latest Common Month in a Country) for flag in in Power query .On the basis Country ,Category ,Year , Month No. I am trying to find the Latest month in a country Flag. The data looks like below:

Country	Category	Year	Month No	Latest Common Month flag in a Country considering Category	Latest common month flag availbale  in all the country without considering Category
US	Toothpaste	2021	8	1	1
US	Sports	2021	9	0	0
US	Sports	2021	8	1	1
US	Sports	2020	8	0	0
US	Toothpaste	2019	10	0	0
US	Sports	2019	10	0	0
US	Sports	2018	10	0	0
UK	Toothpaste	2021	8	1	1
UK	Sports	2021	8	1	1
UK	Sports	2021	9	0	0
UK	Toothpaste	2020	12	0	0
UK	Sports	2020	12	0	0
UK	Toothpaste	2019	10	0	0
UK	Sports	2019	10	0	0
Aus	Toothpaste	2021	9	1	0
Aus	Sports	2021	9	1	0
Aus	Toothpaste	2021	8	0	1
Aus	Sports	2020	12	0	0
Aus	Toothpaste	2019	10	0	0
Aus	Sports	2019	10	0	0
RUS	Toothpaste	2021	8	1	1
RUS	Sports	2021	8	1	1

Please help me to find the latest common month in each country considering Category and  latest common month in all the countries without considering category column

[ronrsnfld · Accepted solution]

See the latest version of my answer to your identical question at https://stackoverflow.com/questions/69572644/latest-common-month-in-a-country-considering-category 

[v-yingjl]

Hi @Anonymous ,

You can try this query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZK9DoAgDITfhdmhZZLR2c2fyTg4mLhhBN9fNERNLZbhQghfrtylw6D6VhWqs9Yv6+T8HC4aNIajDMJLYxGxdrWbdw9iguBSEhFcICLEhfwHz0EIv9OymJJh6rz8tRyOQ4w87HxG/eVoTxyTUxTx4phqd4kWTIz45riMlEk1CnddXy8mJGuWTiDEbDKXvZFWeTwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Country = _t, Category = _t, Year = _t, #"Month No" = _t, #"Latest Common Month flag in a Country considering Category" = _t, #"Latest common month flag availbale  in all the country without considering Category" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Country", type text}, {"Category", type text}, {"Year", Int64.Type}, {"Month No", Int64.Type}, {"Latest Common Month flag in a Country considering Category", Int64.Type}, {"Latest common month flag availbale  in all the country without considering Category", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Country", "Category", "Year"}, {{"Data", each _, type table [Country=nullable text, Category=nullable text, Year=nullable number, Month No=nullable number, Latest Common Month flag in a Country considering Category=nullable number, Latest common month flag availbale  in all the country without considering Category=nullable number]}}),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([Year] = List.Max(#"Grouped Rows"[Year]))),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Month No", each Table.Max([Data],"Month No")[Month No],Int64.Type),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Data"}),
    #"Grouped Rows1" = Table.Group(#"Removed Columns", {"Country"}, {{"Data", each _, type table [Country=nullable text, Category=nullable text, Year=nullable number, Month No=number]}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows1", "Min Month", each Table.Min([Data],"Month No")[Month No],Int64.Type),
    #"Expanded Data" = Table.ExpandTableColumn(#"Added Custom1", "Data", {"Category", "Year", "Month No"}, {"Category", "Year", "Month No"}),
    #"Filtered Rows1" = Table.SelectRows(#"Expanded Data", each ([Month No] = [Min Month])),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows1",{"Min Month"})
in
    #"Removed Columns1"

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[smpa01]

@Anonymous  the latest common month by US-2021 is 8 for both Toothpaste and Sports. WHy do you have 8 for Toothpaste and 9 for Sports?

[lbendlin]

you can do that with a couple of groupings in Power Query.  Please provide sanitized sample data that fully covers your issue. Paste the data into a table in your post or use one of the file services.

[Anonymous]

pasted as table ..thanks