List.generate help

[Jurekbartlomiej]

Hi everyone !

Based on the data from the "Input" table, I would like to get the data as in the "Output" table.

I tried to use list.generate, however, I can't get over it yet. 

code:

List.Generate(
()=>[DateStart],
each _>[DateEnd],
each Date.AddMonths(_,1)
)

erorr:

Expression.Error: We cannot apply field access to the type Date.
Details:
Value=01.01.2022
Key=DateEnd

all code:

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"DateStart", type date}, {"DateEnd", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Generate(
()=>[DateStart],
each _>[DateEnd],
each Date.AddMonths(_,1)
)),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom")
in
#"Expanded Custom"

[wdx223_Daniel]

let
 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
 #"Changed Type" = Table.TransformColumnTypes(Source,{{"DateStart", type date}, {"DateEnd", type date}}),
 #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", (x)=>List.Generate( ()=>x[DateStart], each _<x[DateEnd], each Date.AddMonths(_,1) )),
 #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom") 
in
 #"Expanded Custom"

[Daryl-Lynch-Bzy]

Hi @Jurekbartlomiej  - @alena2k has provide a good solution, but I wanted you to highlight the following:
(1) note we have changed the > to < for this step "each _> _[DateEnd]".  It is corrected to "_ < _[DateEnd]"
(2) why it is not working for the future reference. 

In the following step from your code:

#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Generate(
()=>[DateStart],
each _>[DateEnd],
each Date.AddMonths(_,1)
))

Power Query has lost visibility of the [DateStart] and [DateEnd] from the original table #"Change Type" when List Generate invoked new each step.  The above can be rewritten like this, but Power Query will be confused:

#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", (_) => List.Generate(
(_)=> _[DateStart],
each _> _[DateEnd],
each Date.AddMonths(_,1)
))

The second (_) effective replaces the first (_), so Power Query does not know where to look for [DateStart] and [DateEnd].  So when @alena2k suggest to use the function, the values of the date are pass from table to the function then to List Generate.

It possible to include this as a nested step, but an alias is required for the table to avoid confusion.  It look like this:

= Table.AddColumn(#"Changed Type", "Custom", 
(a) => 
    List.Generate( 
        () => a[DateStart],
        each _ < a[DateEnd],
        each Date.AddMonths( _ ,1)
    ), type list)

[alena2k]

Hi @Jurekbartlomiej you can do it if you move list generation into the function and then add column via Invoke Custom Function

getDates

(DateStart as date, DateEnd as date) =>
let
Source = List.Generate(()=> DateStart,each _ < DateEnd,each Date.AddMonths(_,1))
in
Source

main query
let
Source = .....,
#"Invoked Custom Function" = Table.AddColumn(Source, "Custom", each getDates([startDate], [endDate])),
#"Expanded Custom" = Table.ExpandListColumn(#"Invoked Custom Function", "Custom"),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", type date}})
in
#"Changed Type"