Help with List.Generate between two dates

[tiagocarvalhal]

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,

[tiagocarvalhal]

Hi @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,

[olgad · Accepted solution]

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/?tr...