Adding values from table to list

[RvanMelis]

Hello, 

Before i'm asking for help i'll tell you guys what i want to create. 

I've got a table with a column startdate, and a column with monthsleft. I want to create a list with dates that start with the column startdatumtest and repeat based on the column monthsleft. 

I'm using List.Transform({0..[Monthsleft]}, each Date.AddMonths(DateTime.LocalNow(), _)) and thats working. But it starts from LocalNow and i want to start based on column StartDatumTest.

I tried replacing LocalNow with column StartDatumTest, tried to change column type to Date/DateTime but i'm not able to get it to work. Is it even possible to use a column from the table to create this?

Below there is a screenshot about the situation. 

Best regards.

Rolán

[RvanMelis]

Thanks for the quick response. 

I think it works, but i'm curious if i did it the right way... I'll explain what i did, curious about feedback. 

Step 1: Removed the Listin column

Step 2: Added New Custom Column

Step 3: Put in this part of your code and replaced some values based on my table: 

Table.AddColumn(
#"Changed Type",
"Test Column",
each
let
varStartDate = [Today]
in
List.Transform(
{0..[Monthsleft]},
each Date.AddMonths(varStartDate, _)
)
)

Step 4: Add new Query based on new column 'Listin'

Step 5: Rename table and use this table

Is this the correct way? Is there a way to add a list or is it always a table?

[edhans]

I'm not sure I'm following. I am not sure what "the correct way" is. You haven't really explained what your overall goal is. You just asked why couldn't you use your [Today] field vs a formula, and I showed how to use a variable to do that. 

This might be the best way, or a good way, or it might not. I have only seen a tiny snippet of your project, and the overall question might be better in a new thread with an overall discussion of your goals.

[edhans · Accepted solution]

Try this code @RvanMelis - [Start Date] cannot be directly accessed within the List.Transform function that way, so you need to capture it as a variable before List.Transform, then use the variable.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJU0lEyVIrViVYyQQgYK8XGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [StartDate = _t, #"Months Left" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"StartDate", type date}, {"Months Left", Int64.Type}}),
    #"Added Custom" = 
        Table.AddColumn(
            #"Changed Type", 
            "Test Column", 
            each
                let
                    varStartDate = [StartDate]
                in
                List.Transform(
                    {0..[Months Left]}, 
                    each Date.AddMonths(varStartDate, _)
                )
            )
in
    #"Added Custom"

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.