Date Max - Power Query

[davehus]

Hi - I'm trying to get the Max date in my date table but when I use List.Max I get an error. 

Any ideas what I'm doing wrong?

[v-danhe-msft]

Hi @davehus,

Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?

Regards,

Daniel He

[Interkoubess]

Hey @davehus,

Your formula does not work because you did not apply it to a list.

Please use the following formula ( if it does not work, then provide previous step and I will modify):

= Table.AddColumn(#"Changed Type2", "Custom", each List.Max(#"Changed Type2"[Datekey]))

Hope it helps....

Ninter

[davehus]

Hi @Interkoubess,

Thanks for your help, I'm still getting an error though. I've included the full M code for you to review.

let
    Source = #date(2018, 1, 1),
    InvokeCal = List.Dates(Source, Number.From(DateTime.LocalNow())-Number.From(Source), #duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(InvokeCal, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "DateKey"}}),
    #"Inserted Month" = Table.AddColumn(#"Renamed Columns", "Month", each Date.Month([DateKey]), Int64.Type),
    #"Inserted Month Name" = Table.AddColumn(#"Inserted Month", "Month Name", each Date.MonthName([DateKey]), type text),
    #"Inserted Quarter" = Table.AddColumn(#"Inserted Month Name", "Quarter", each Date.QuarterOfYear([DateKey]), Int64.Type),
    #"Inserted Week of Year" = Table.AddColumn(#"Inserted Quarter", "Week of Year", each Date.WeekOfYear([DateKey]), Int64.Type),
    #"Inserted Day of Week" = Table.AddColumn(#"Inserted Week of Year", "Day of Week", each Date.DayOfWeek([DateKey]), Int64.Type),
    #"Inserted Day Name" = Table.AddColumn(#"Inserted Day of Week", "Day Name", each Date.DayOfWeekName([DateKey]), type text),
    #"Inserted Year" = Table.AddColumn(#"Inserted Day Name", "Year", each Date.Year([DateKey]), Int64.Type),
    #"Changed Type1" = Table.TransformColumnTypes(#"Inserted Year",{{"Year", type text}, {"Week of Year", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Year Week", each [Year]&Text.PadStart(Text.From([Week of Year]),2,"0")),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom",{{"Year Week", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type2", "Index", 1, 1),
    #"Removed Columns" = Table.RemoveColumns(#"Added Index",{"Index"}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type2", "Custom", each List.Max(#"Changed Type2"[Datekey]))
in
    #"Added Custom1"

Thanks,

D

[Interkoubess · Accepted solution]

Hi @davehus,

Then use this formula ( only changed previous step 😞

let
    Source = #date(2018, 1, 1),
    InvokeCal = List.Dates(Source, Number.From(DateTime.LocalNow())-Number.From(Source), #duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(InvokeCal, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "DateKey"}}),
    #"Inserted Month" = Table.AddColumn(#"Renamed Columns", "Month", each Date.Month([DateKey]), Int64.Type),
    #"Inserted Month Name" = Table.AddColumn(#"Inserted Month", "Month Name", each Date.MonthName([DateKey]), type text),
    #"Inserted Quarter" = Table.AddColumn(#"Inserted Month Name", "Quarter", each Date.QuarterOfYear([DateKey]), Int64.Type),
    #"Inserted Week of Year" = Table.AddColumn(#"Inserted Quarter", "Week of Year", each Date.WeekOfYear([DateKey]), Int64.Type),
    #"Inserted Day of Week" = Table.AddColumn(#"Inserted Week of Year", "Day of Week", each Date.DayOfWeek([DateKey]), Int64.Type),
    #"Inserted Day Name" = Table.AddColumn(#"Inserted Day of Week", "Day Name", each Date.DayOfWeekName([DateKey]), type text),
    #"Inserted Year" = Table.AddColumn(#"Inserted Day Name", "Year", each Date.Year([DateKey]), Int64.Type),
    #"Changed Type1" = Table.TransformColumnTypes(#"Inserted Year",{{"Year", type text}, {"Week of Year", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Year Week", each [Year]&Text.PadStart(Text.From([Week of Year]),2,"0")),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom",{{"Year Week", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type2", "Index", 1, 1),
    #"Removed Columns" = Table.RemoveColumns(#"Added Index",{"Index"}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type2", "Custom", each List.Max(#"Removed Columns"[Datekey]))
in
    #"Added Custom1"

Hope it helps....

Ninter