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 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

Need Help
  • edhans's avatar
    edhans
    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.

     

3 Replies

  • edhans's avatar
    edhans
    Community Champion
    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.

     

  • RvanMelis's avatar
    RvanMelis
    Frequent Visitor
    5 years ago

    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's avatar
      edhans
      Community Champion
      5 years ago

      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.