Forum Discussion

RvanMelis's avatar
RvanMelis
Frequent Visitor
5 years ago
Solved

Adding values from table to list

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 s...
  • edhans's avatar
    5 years ago

    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.