Excel Power Query: Adding Missing Dates to a Table

Question

Hi guys,

I need your help please.

As you could see the below table screen shot got a column that has a list of dates, but it misses some days in the middle. I hope to build a query that add for me the missing days. The start and end dates in the desired series should be the oldest and the latest date in the existing column consecutively, how could I do that?

(Image #1)

I applied this code that got me a part of the solution but created for me another problem

let
Source = Excel.CurrentWorkbook(){[Name="Table6"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{<!-- -->{"Name", type text}, {"Date", type date}, {"Schedule", type text}}),
Dates = Table.FromColumns({List.Transform({Number.From( (Date.From(List.Min(#"Changed Type"[Date]))))..Number.From( (Date.From(List.Max(#"Changed Type"[Date]))))}, Date.From)}, {"Date"}),
Merged = Table.NestedJoin(Dates, {"Date"}, #"Changed Type", {"Date"}, "Gr", JoinKind.LeftOuter),
Expanded = Table.ExpandTableColumn(Merged, "Gr", {"Name", "Schedule"}, {"Name", "Schedule"}),
Reordered = Table.ReorderColumns(Expanded,{"Name", "Date", "Schedule"}),
#"Filled Down" = Table.FillDown(Reordered,{"Name"}),
#"Replaced Value" = Table.ReplaceValue(#"Filled Down",null,"Weekend",Replacer.ReplaceValue,{"Schedule"}),
#"Sorted Rows" = Table.Sort(#"Replaced Value",{<!-- -->{"Name", Order.Ascending}, {"Date", Order.Ascending}})
in
#"Sorted Rows"

And this got me the below output:

Image #2

Now, problem is happening when there is more than 1 name in the data source table like the below:

Here we get undesirable output and the transformation is applied only in the latest name in the list but not on the rest of the names

Image #3

Answer 1

Hi @Magdoulin Shams El-Din ,

In the Power Query editor create a New Blank query ( Home Tab -> New Sources -> Blank Query), then inth Query1 that is generate in left panel, right clic and press advanced editor, delete the code in there and paste the following:

https://gist.githubusercontent.com/ExcelFreeBlog/da0338ba898def8b2f042f30476baa17/raw/d77307b0fb73182517921cd59ed1d95215f9e7e8/MagdoulinShamsElDin-9646

Note that it is a function, so load your table afterwards and use the indicated function, the result looks like

Keep in mind that my regional configuration for dates is dd / mm / yyyy while the one presented in the image is m / dd / yyyy, however, this does not affect the proper work of the function

Finally rename the columns as you need and assign proper data type to each column

— Miguel Caballero

Answer 2

Your algorithm is basically OK, except that, with multiple names, you can:

• Group by Name
• Create full date list for each Name
• Join the sub tables
• Do the housekeeping (sorting; replacing null with weekend, etc

Creating the datelist for each Name, within the Group function, cannot be done within the UI, but must be done in the Advanced Editor.

M Code

  let  
        Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],  
        #"Changed Type" = Table.TransformColumnTypes(Source,{<!-- -->{"Name", type text}, {"Date", type date}, {"Schedule", type text}}),  
    //Group by Name  
    //Then create Table of dates for each subgroup  
        #"Grouped Rows" = Table.Group(#"Changed Type", {"Name"}, {  
                {"All", each _, type table [Name=nullable text, Date=nullable date, Schedule=nullable text]},  
                {"All Dates", each Table.FromList(  
                        List.Dates(List.Min([Date]),   
                            Duration.TotalDays(List.Max([Date])-List.Min([Date]))+1,  
                            #duration(1,0,0,0)),Splitter.SplitByNothing(),{"allDates"})}             
                }),  
      
    //Join the two tables with a RightOuter join,  
    //Then "fill down" the missing names in the Name column  
        #"Added Custom" = Table.AddColumn(#"Grouped Rows", "mergedDates",   
            each Table.FillDown(  
                Table.Join([All],"Date",[All Dates],"allDates",JoinKind.RightOuter),{"Name"})),  
      
    //Housekeeping  
        #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Name", "All", "All Dates"}),  
        #"Expanded mergedDates" = Table.ExpandTableColumn(#"Removed Columns", "mergedDates",   
            {"Name", "Schedule", "allDates"}, {"Name", "Schedule", "allDates"}),  
        #"Changed Type1" = Table.TransformColumnTypes(#"Expanded mergedDates",{  
            {"allDates", type date}, {"Name", type text}, {"Schedule", type text}}),  
        #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{<!-- -->{"allDates", "Date"}}),  
        #"Sorted Rows" = Table.Sort(#"Renamed Columns",{<!-- -->{"Name", Order.Ascending}, {"Date", Order.Ascending}}),  
        #"Reordered Columns" = Table.ReorderColumns(#"Sorted Rows",{"Name", "Date", "Schedule"}),  
        #"Replaced Value" = Table.ReplaceValue(#"Reordered Columns",null,"Weekend",Replacer.ReplaceValue,{"Schedule"})  
    in  
        #"Replaced Value"  

Answer 3

Hi Everyone,

Apart from the solution that I provide in the first answer using: Group by and Merge

I have also created another soluction (more programmatically) with List.TrasnformMany:

// Another way:

( Tabla as table ) as table =>
let
    s0 = 
    List.TransformMany (
        Table.ToList ( 
            Table.Distinct ( Tabla, { "Name", "Schedule" } ), 
            each _ 
        ),

        ( a ) =>
        let
            DatesOfName = 
            Table.SelectRows ( Tabla, each [Name] = a{0} )[Date],

            DatesGeneration =
            List.Dates ( 
                List.Min ( DatesOfName ), 
                Number.From ( 
                    List.Max ( DatesOfName ) 
                        - List.Min ( DatesOfName ) 
                ) + 1, 
                #duration ( 1, 0, 0, 0 )  
            )
        in
        DatesGeneration,  

        ( a, b ) =>
        [ 
            Name = a{0}, 
            Date = b, 
            Schedule = 
            if
            List.IsEmpty ( List.Intersect ( { Table.SelectRows ( Tabla, each [Name] = a{0} )[Date], {b} } ) )
            then
                "Weekend"
            else
                a{2}

        ]
    ),

    s1 = 
    Table.FromValue (
        s0
    ),

    s3 = 
    Table.ExpandRecordColumn ( 
        s1, 
        "Value", 
        { "Name", "Date", "Schedule" }, 
        { "Name", "Date", "Schedule" }
    )

in
    s3

//

But the first it's the best solution, nevertheless, I offer this option for educational purposes and have the possibility to consult this option later, any optimization suggestion in the code is welcome

— Miguel Caballero

Answer 4

Excel 365 Pro Plus with Power Pivot and Power Query.
With Parameters.
https://www.mediafire.com/file/3lef82ec5rrx3ab/08_28_21b.xlsx/file
https://www.mediafire.com/file/7fpr79b48ev1rj0/08_28_21b.pdf/file

Answer 5

thank you all for your help, I'm quite sure your solutions work, it's just I got the needed support from another forum earlier and the code worked for me which is (for the reference):

let

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8k1MT8kvzcnMU9JRstA30jcyMDIEMoMzMtNKlGJ10BUYE1JgQkiBKSEFZoQUWBJSYGhAUIUhQRUEg8KQYFgYYvFKREVFBVDAHFtQw+WwmAyXwxLAcDkMj8cCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Date = _t, Schedule = _t]),

DateType = Table.TransformColumnTypes(Source,{{"Date", type date}}),

Grouped = Table.Group(DateType, {"Name"}, {{"Date", each List.Transform({Number.From( (Date.From(List.Min([Date]))))..Number.From( (Date.From(List.Max([Date]))))}, Date.From) }}),

"Expanded Gr" = Table.ExpandListColumn(Grouped, "Date"),

"ChangedType" = Table.TransformColumnTypes(#"Expanded Gr",{{"Date", type date}}),

"FINAL" = Table.AddColumn(ChangedType, "Schedule", each if Date.DayOfWeek([Date],Day.Monday) >4 then "Weekend" else "Shift")// or do a merge with Source table if you have some other condition

in

"FINAL"