Finding max function in custom column in Power query

Question

I am trying to create two custom column for flag in in Power query .On the basis County ,Year , month I am trying to find the Latest month in a country Flag and latest common month available in all the country.The data looks like below:

I have tried below measure...: =var year_x= CALCULATE(MAX([Year]),ALLEXCEPT('tblename','tablename'[Country])) var month_x = CALCULATE(max([Month No]),FILTER(ALLEXCEPT('tablename','tablename'[CountryI]),'tablename'[Year]=year_x))

return month_x

But this will not solve my problem as I want to create one custom flag in the power query.I know we can do this in Power BI...but no idea in Excel power query

Please help me to find one option for this

Answer 1

Try

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Custom" = Table.AddColumn(Source, "Custom", each Number.From([Year])*100+Number.From([Month No])),
#"Grouped Rows" = Table.Group(#"Added Custom", {"Country"}, {{"Count", each List.Max([Custom]), type number}}),
Maxcommon =  List.Min(Table.Group(#"Added Custom", {"Country"}, {{"Count", each List.Max([Custom]), type number}})[Count]),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "latest common", each if [Custom]=Maxcommon  then 1 else 0),
#"Merged Queries" = Table.NestedJoin(#"Added Custom1" ,{"Country"},#"Grouped Rows",{"Country"},"Table2",JoinKind.LeftOuter),
#"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"Count"}, {"Count"}),
#"Added Custom2" = Table.AddColumn(#"Expanded Table2", "latest month Flag", each if [Custom]=[Count] then 1 else 0),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Custom", "Count"})
in #"Removed Columns"

Answer 2

Try:

M-Code

let
    Source = Excel.CurrentWorkbook(){[Name="Table20"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,
        {{"Country", type text}, {"Year", Int64.Type}, {"Month No", Int64.Type}}),

//Country List
countries = List.Distinct(Table.Column(#"Changed Type","Country")),

//Calculate full date
    #"Added Custom" = Table.AddColumn(#"Changed Type", "fullDate", each #date([Year],[Month No],1),type date),

//determine latest month flag by country
#"Grouped Rows" = Table.Group(#"Added Custom", {"Country"}, {{"grouped", 
            each _, type table [Country=nullable text, Year=nullable number, Month No=nullable number, fullDate=date]}, 
                                {"latest fullDate", each List.Max([fullDate]), type date}}),
    #"Expanded grouped" = Table.ExpandTableColumn(#"Grouped Rows", "grouped", {"Year", "Month No", "fullDate"}, {"Year", "Month No", "fullDate"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded grouped", "Latest Month Flag", each if [latest fullDate] = [fullDate] then 1 else 0),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"latest fullDate"}),

//Determine latest month flag for ALL countries
    #"Grouped Rows1" = Table.Group(#"Removed Columns", {"fullDate"}, {{"Grouped", each _, type table [Country=nullable text, Year=nullable number, Month No=nullable number, fullDate=nullable date, Latest Month Flag=number]}}),
    #"Added Custom2" = Table.AddColumn(#"Grouped Rows1", "Latest month ALL countries", each List.Count(
       List.RemoveMatchingItems(countries, Table.Column([Grouped],"Country"))) = 0),
    #"Grouped Rows2" = Table.Group(#"Added Custom2", {"Latest month ALL countries"}, {{"Grouped", each _, type table [fullDate=nullable date, Grouped=table, Latest month ALL countries=logical]}, {"maxAll", each List.Max([fullDate]), type nullable date}}),
    #"Expanded Grouped" = Table.ExpandTableColumn(#"Grouped Rows2", "Grouped", {"fullDate", "Grouped"}, {"fullDate", "Grouped.1"}),
    #"Added Custom3" = Table.AddColumn(#"Expanded Grouped", "Latest month ALL countries Flag", each if [maxAll] = [fullDate] and 
      [Latest month ALL countries] = true 
      then 1 else 0),
    #"Expanded Grouped.1" = Table.ExpandTableColumn(#"Added Custom3", "Grouped.1", {"Country", "Year", "Month No", "fullDate", "Latest Month Flag"}, {"Country", "Year", "Month No", "fullDate.1", "Latest Month Flag"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded Grouped.1",{"Latest month ALL countries", "fullDate", "fullDate.1", "maxAll"}),
    #"Sorted Rows" = Table.Sort(#"Removed Columns1",{{"Country", Order.Descending}, {"Year", Order.Ascending}, {"Month No", Order.Ascending}})
in
    #"Sorted Rows"

Original Data

Results

*Note: If there are no common dates in one of the countries, there will be no flag in the ALL countries column