Forum Discussion
Help with List.Generate between two dates
Hi everyone,
I´m trying to get a list of months between two dates using List.Generate. When I try compare a column with a specific date like today, it´s is working perfect. The problem is when I try to compare two columns. Can you help me please?
This is the code that is giving me error:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDU9csv0zUyMLRQ0lHKK83JUYrVAQu7pCbDhJEVocpaQmThalFkjQxQZC1hsm6pSSBZQ2RZoFpUvVBZuFpUWSMUvUDZWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Previous Date" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Previous Date", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom",
each if [Previous Date] <> null and Duration.Days([Date] - [Previous Date] )>31 then
List.Generate(()=>
[x=[Previous Date], y=[Date]],
each Duration.Days([y] - [x] ) > 31,
each [x= Date.AddMonths([x],1)],
each [x])
else null)
in
#"Added Custom"
I've tried other approach but with the same error:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDU9csv0zUyMLRQ0lHKK83JUYrVAQu7pCbDhJEVocpaQmThalFkjQxQZC1hsm6pSSBZQ2RZoFpUvVBZuFpUWSMUvUDZWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Previous Date" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Previous Date", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom",
each if [Previous Date] <> null and Duration.Days([Date] - [Previous Date] )>31 then
List.Generate(()=>
[x=[Previous Date], y=[Date]],
each [x]<[y],
each [x= Date.AddMonths([x],1)],
each [x])
else null)
in
#"Added Custom"
Thanks,
Hi, use the custom column
{Number.From([Start Date])..Number.From([Exit Date]) }
then expand the list .This article of mine describes nicely how to generate those dates
https://www.linkedin.com/pulse/hr-reporting-generating-records-between-start-end-values-dontsova/?trackingId=MpZxmQguRYuOR%2BXY8OmiLA%3D%3D
3 Replies
Hi, use the custom column
{Number.From([Start Date])..Number.From([Exit Date]) }
then expand the list .This article of mine describes nicely how to generate those dates
https://www.linkedin.com/pulse/hr-reporting-generating-records-between-start-end-values-dontsova/?trackingId=MpZxmQguRYuOR%2BXY8OmiLA%3D%3DHi olgad ,
Thanks for your support, that can works, however I need some additional steps to get the output. Do you know why the "each [x]<[y]," doesn´t work?
With your inputs the final query looks like:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDU9csv0zUyMLRQ0lHKK83JUYrVAQu7pCbDhJEVocpaQmThalFkjQxQZC1hsm6pSSBZQ2RZoFpUvVBZuFpUWSMUvUDZWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Previous Date" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Previous Date", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom",
each if [Previous Date] <> null and Duration.Days([Date] - [Previous Date] )>31 then
{Number.From([Previous Date])..Number.From([Date])}
else {[Date]}),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", type date}}),
#"Calculated Start of Month" = Table.TransformColumns(#"Changed Type1",{{"Custom", Date.StartOfMonth, type date}}),
#"Removed Duplicates" = Table.Distinct(#"Calculated Start of Month", {"Custom"})
in
#"Removed Duplicates"Thanks,
I am using this to generate rows of dates between two dates with varying frequencies in power query
let freq = [FREQUENCY] in let enddate = [End Date] in List.Generate(()=> [Start Date], each Date.From(_) <=Date.From(enddate), each Date.AddDays(_,freq))
Question
is there a way to exclude the start date when generating the rows of dates, I need the list to be exclusive of the start and end dates and have them not included in the list. Hopefully without changing the whole line of code entirely
