Tuesday, 1 August 2023

P5

 let

    // Assuming "Table" is the name of your table and "Date" is the name of your date column


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

    DatesList = List.Sort(List.Distinct(Table[Date])),


    // 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())

in

    Table.AddColumn(Table, "FilledDate", each FilledDates{[DateIndex]})


No comments:

Post a Comment