Forum Discussion
M Query: Add list of months between dates
I'm trying to add rows for each month between two dates. I found some example code on the forums and tried to adapt it for my purposes, but it isn't working properly. I'm adding a custom column named "DateRange" and using the following code.
Table.AddColumn(#"Expanded people", "DateRange", (earlier) => List.Generate(
() => [EffortDate = earlier[award_begin_date], Counter = earlier[Months]],
each [EffortDate] <= earlier[award_end_date],
each [EffortDate = Date.AddMonths(earlier[award_begin_date],[Counter]),
Counter = [Counter] + earlier[Months]],
each [EffortDate]),
type {date})
It says there are no syntax errors. I expand the resulting Table column and then when I try to expand the resulting List column into new rows, I just get errors. Any help would be appreciated!
Hi, Anonymous , don't bother to use List.Generate(); List.Accumulate() would come in handy in your senario. You might want to try,
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTLRN9Q3MjC0BDKN9Y1BbCMDpVidaCUjoIgZRNICyDRFSMYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, award_begin_date = _t, award_end_date = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"award_begin_date", type date}, {"award_end_date", type date}}), #"Added Custom" = Table.AddColumn( #"Changed Type", "col", each let begin = Date.StartOfMonth([award_begin_date]) in List.Accumulate( {0..(Date.Year([award_end_date])-Date.Year([award_begin_date]))*12+(Date.Month([award_end_date])-Date.Month([award_begin_date]))}, {}, (s,c) => s&{Date.AddMonths(begin,c)} ) ), #"Expanded col" = Table.ExpandListColumn(#"Added Custom", "col") in #"Expanded col"
8 Replies
- HotChilli
Community Champion
Please post some data (not a picture) and someone will help.
It's literally just three columns:
ID award_begin_date award_end_date 1 4/1/2019 3/31/2020 2 6/1/2018 5/31/2020I want it to expand to be:
ID award_begin_date award_end_date DateRange 1 4/1/2019 3/31/2020 4/1/2019 1 4/1/2019 3/31/2020 5/1/2019 1 4/1/2019 3/31/2020 6/1/2019 1 4/1/2019 3/31/2020 7/1/2019 1 4/1/2019 3/31/2020 8/1/2019 1 4/1/2019 3/31/2020 9/1/2019 1 4/1/2019 3/31/2020 10/1/2019 1 4/1/2019 3/31/2020 11/1/2019 1 4/1/2019 3/31/2020 12/1/2019 1 4/1/2019 3/31/2020 1/1/2020 1 4/1/2019 3/31/2020 2/1/2020 1 4/1/2019 3/31/2020 3/1/2020 2 6/1/2018 5/31/2020 6/1/2018 2 6/1/2018 5/31/2020 7/1/2018 ...
CNENFRNLHi, Anonymous , don't bother to use List.Generate(); List.Accumulate() would come in handy in your senario. You might want to try,
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTLRN9Q3MjC0BDKN9Y1BbCMDpVidaCUjoIgZRNICyDRFSMYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, award_begin_date = _t, award_end_date = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"award_begin_date", type date}, {"award_end_date", type date}}), #"Added Custom" = Table.AddColumn( #"Changed Type", "col", each let begin = Date.StartOfMonth([award_begin_date]) in List.Accumulate( {0..(Date.Year([award_end_date])-Date.Year([award_begin_date]))*12+(Date.Month([award_end_date])-Date.Month([award_begin_date]))}, {}, (s,c) => s&{Date.AddMonths(begin,c)} ) ), #"Expanded col" = Table.ExpandListColumn(#"Added Custom", "col") in #"Expanded col"
