Create New Date/Month List based on Earliest and Last Dates in existing column

[mattmattmatt]

Hello,

This is my first time posting here (thanks in advance for your help). I would like to do two things:

1.) Create a new column to populate all dates (month-year), starting with the earliest date (Jan 2022) and going through largest date (August 2022). So it would have Jan, Feb, March...Aug 2022. I believe this is achieved with List.Generate but let me know if there is a better way.

2.) After creating this list, I need to create a "New Value" column which either (i) pulls the existing Value if the date existed in the original data, or alternatively (ii) if the data did not exist, then simply pull copy down the latest value that did exist. So in the below example, it would have 4.3 for Jan + Feb 2022, 4.5 for March - July 2022, and 4.7 for August 2022.

Value	Date
4.3	1/1/2022
4.5	3/1/2022
4.7	8/1/2022

Is this easy to build?

[mattmattmatt]

Thank you @slorin ! I got it to work with your code.

[slorin · Accepted solution]

Hi @mattmattmatt 

let
Source = Your_Source,
List_Month = Table.FromColumns(
    {List.Select(
        List.Transform(
            {Number.From(List.Min(Source[Date]))..Number.From(List.Max(Source[Date]))},
            Date.From),
        each Date.Day(_)=1)},
    type table [Date = date]),
Join = Table.NestedJoin(List_Month, {"Date"}, Source, {"Date"}, "Source", JoinKind.LeftOuter),
Expand = Table.ExpandTableColumn(Join, "Source", {"Value"}, {"Value"}),
FillDown = Table.FillDown(Expand,{"Value"})
in
FillDown

 Stéphane