Finding max function in custom column in Power query

0
votes

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: enter image description here

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

2
excelpowerbipowerquery
That looks like dax, not power query – W.B.
I tried in measure...But I want to build a custom column as shown in the table – faisal
You write On the basis County ,Year , month. But do you just want to take into account month for the Latest month available all the country Flag? Else what is the logic for Aus when 11/2020 is not available in other countries – Ron Rosenfeld
Hi Ron.thanks ....just edited the table..second flag is for latest common month available in all the country..I am considering year and month for this latest common month consideration – faisal
Thanks for answer...I am trying to use your code...could you republish – faisal

2 Answers

1
votes

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"
1
votes

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

enter image description here

Results

enter image description here

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