Forum Discussion

Namoh's avatar
Namoh
Post Partisan
6 years ago
Solved

Max value from a column in Power Query Editor

Hi. I've got a very easy question, but somehow I can't get it to work. I have a table with multiple columns, one of them is called FiscalYear. I want to add another Calculated Column which shows t...
Need Help
  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    6 years ago

    Hi Namoh ,

     

    You need to add #”Change Type” before [FiscalYear].

     

     

    If you have any question, please kindly ask here and we will try to resolve it.

     

    Best regards,

     

    Community Support Team _ zhenbw

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    BTW, pbix as attached.

Ashish_Mathur's avatar
Ashish_Mathur
Super User
6 years ago

Hi,

I am sure there is a better way but this M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"FiscalYear", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each 1),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Custom"}, {{"Max", each List.Max([FiscalYear]), type number}}),
    Joined = Table.Join(#"Added Custom", "Custom", #"Grouped Rows", "Custom"),
    #"Removed Columns" = Table.RemoveColumns(Joined,{"Custom"})
in
    #"Removed Columns"

JoseAndres's avatar
JoseAndres
Frequent Visitor
3 years ago

Thanks

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    3 years ago

    You are welcome.