Tuesday, 1 August 2023

P7

 let

    // Assuming "Calendar3" is the name of your table and "StartDate" is the name of your date column


    // Create a list of all unique dates in the "StartDate" column

    DatesList = List.Sort(List.Distinct(Calendar3[StartDate])),


    // Find the minimum and maximum dates from the list

    MinDate = List.Min(DatesList),

    MaxDate = List.Max(DatesList),


    // Generate a list of all dates between the minimum and maximum dates

    AllDates = List.Dates(MinDate, Duration.From(1), Duration.From(1)),


    // Add a custom column to fill missing dates

    CustomColumn = List.Generate(

        () => [DateIndex = 0, CurrentDate = MinDate],

        each [DateIndex] < List.Count(AllDates),

        each [DateIndex = [DateIndex] + 1, CurrentDate = AllDates{[DateIndex]}],

        each [CurrentDate]

    ),

    FilledDates = Table.FromList(CustomColumn, Splitter.SplitByNothing()),


    // Merge the filled dates back into the "Calendar3" table

    MergedTable = Table.NestedJoin(Calendar3, {"StartDate"}, FilledDates, {"Column1"}, "Expanded", JoinKind.LeftOuter),


    // Expand the filled dates column

    ExpandedTable = Table.ExpandTableColumn(MergedTable, "Expanded", {"Column1"}, {"FilledDate"}),


    // Remove the temporary column used for the join

    CleanedTable = Table.RemoveColumns(ExpandedTable, {"Column1"})

in

    CleanedTable


No comments:

Post a Comment