Forum Discussion
Adding values from table to list
- 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.
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?
- edhans5 years agoCommunity Champion
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.