Forum Discussion
Solved
Date Max - Power Query
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?
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
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
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
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