Forum Discussion

Jurekbartlomiej's avatar
Jurekbartlomiej
New Member
3 years ago

List.generate help


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"

 

 

Need Help

3 Replies

  • alena2k's avatar
    alena2k
    Resolver IV
    3 years ago

    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"

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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)




  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion
    3 years ago
    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"