Forum Discussion
Anonymous
7 years agoNot applicable
List.Dates between dates
I am looking for a formula that will look at the added date and number of months then add a column for each month. For example Added date is 25/06/2007 and the number of months is 5 the 1st column wo...
- 7 years ago
try this code, you will need to replace the blue part to match your queries
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtNA1MNQ1MFbSUTJWitUBC5nrGpjoGpgChcyUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Added Date" = _t, #"Number Months" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Added Date", type date}, {"Number Months", Int64.Type}}), #"Added Custom1" = Table.AddColumn(#"Changed Type", "Custom", each Table.ToList(Table.TransformColumnTypes(Table.SelectColumns(Table.AddColumn(Table.ExpandListColumn(#table( type table [Date=date,Days=date], {{[Added Date], {1..[Number Months]}}}), "Days"), "Custom", each Date.AddMonths([Date],[Days])), {"Custom"}),{{"Custom", type text}}))), #"Extracted Values" = Table.TransformColumns(#"Added Custom1", {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text}), #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Custom", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Custom.1", "Custom.2", "Custom.3", "Custom.4", "Custom.5", "Custom.6"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom.1", type date}, {"Custom.2", type date}, {"Custom.3", type date}, {"Custom.4", type date}, {"Custom.5", type date}, {"Custom.6", type date}}) in #"Changed Type1"
v-danhe-msft
7 years agoMicrosoft Employee
Hi Anonymous,
Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?
Regards,
Daniel He