cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RvanMelis
New Member

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. 

RvanMelis_0-1627568125910.png

 

Best regards.

Rolán

1 ACCEPTED SOLUTION
edhans
Super User III
Super User III

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"

edhans_0-1627570351629.png

 

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.

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

3 REPLIES 3
RvanMelis
New Member

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?

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
edhans
Super User III
Super User III

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"

edhans_0-1627570351629.png

 

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.

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Kudoed Authors