Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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...
  • Stachu's avatar
    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"