Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
93 | |
75 | |
62 | |
50 |
User | Count |
---|---|
147 | |
107 | |
105 | |
87 | |
61 |