Fill In Missing Dates in Power Query

[ExcelAtEverything]

Hello all,

I was hoping someone could help me to fill in some missing dates in Power Query.

I have a query in which there are 4 separate & unique stores which make up the whole (displayed as Org #'s), with more stores (Org #'s on the way later this year). Each row contains dated sales information. The problem is that when there is a "$0 sales" day like yesterday (which was July 4th & the store was closed), the entire date for that day is omitted from the date column. What I need is to fill in any missing dates for each of the 4 stores (or if it becomes 5 stores, or 6 stores etc).

The one catch is that while I need to fill in the missing dates between the dates showing in the "Date" column, I need the search for the missing dates to be conducted beginning at the earliest date shown in the "Report Start Date" column, & latest date shown in the "Report End Date columns". It just so happens that the earliest and latest date is currently showing in the screenshot below "1/1/2020" & "7/5/2021".

It "appears" from the limited screenshot that the info is very limited in this query, but it's quite lengthy and contains lots of varying info. Here is the current code, followed by a screenshot.

let
    Source = Table.Combine({#"Discounting Details - Line Item Discounts", #"Discounting Details - Order Disocunts"}),
    #"Filtered Rows" = Table.SelectRows(Source, each true),
    #"Replaced Value" = Table.ReplaceValue(#"Filtered Rows",null,"(Order Discount)",Replacer.ReplaceValue,{"Line Item"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value", "Source.Name", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Source.Name.1", "Source.Name.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Source.Name.1", type text}, {"Source.Name.2", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type", "Source.Name.2", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Source.Name.2.1", "Source.Name.2.2"}),
    #"Split Column by Delimiter2" = Table.SplitColumn(#"Split Column by Delimiter1", "Source.Name.2.2", Splitter.SplitTextByEachDelimiter({"s - "}, QuoteStyle.Csv, false), {"Source.Name.2.2.1", "Source.Name.2.2.2"}),
    #"Split Column by Delimiter3" = Table.SplitColumn(#"Split Column by Delimiter2", "Source.Name.2.2.2", Splitter.SplitTextByEachDelimiter({" to "}, QuoteStyle.Csv, false), {"Source.Name.2.2.2.1", "Source.Name.2.2.2.2"}),
    #"Split Column by Delimiter4" = Table.SplitColumn(#"Split Column by Delimiter3", "Source.Name.2.2.2.2", Splitter.SplitTextByEachDelimiter({".xlsx"}, QuoteStyle.Csv, false), {"Source.Name.2.2.2.2.1", "Source.Name.2.2.2.2.2"}),
    #"Removed Columns2" = Table.RemoveColumns(#"Split Column by Delimiter4",{"Source.Name.2.2.1", "Source.Name.1", "Source.Name.2.2.2.2.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns2",{{"Source.Name.2.1", Int64.Type}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type1", "Date", "Date - Copy"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Duplicated Column",{{"Date - Copy", type time}, {"Date", type date}}),
    #"Renamed Columns2" = Table.RenameColumns(#"Changed Type2",{{"Source.Name.2.2.2.1", "Report Start Date"}, {"Source.Name.2.2.2.2.1", "Report End Date"}}),
    #"Renamed Columns" = Table.RenameColumns(#"Renamed Columns2",{{"Date - Copy", "Time"}, {"Source.Name.2.1", "Org #"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Org #", "Date", "Time", "Order ID", "Line Item", "Description", "Type", "Amount", "Amount Used", "Admin"}),
    #"Filtered Rows1" = Table.SelectRows(#"Reordered Columns", each ([Description] <> " Employee discount " and [Description] <> "Emp" and [Description] <> "Emp samples pricing" and [Description] <> "Emp,dis" and [Description] <> "Emp." and [Description] <> "Emp. Dis." and [Description] <> "Emp.dis" and [Description] <> "Emp.dis." and [Description] <> "Emplo" and [Description] <> "Emploee" and [Description] <> "Employ " and [Description] <> "Employe" and [Description] <> "Employee" and [Description] <> "Employee " and [Description] <> "Employee  " and [Description] <> "Employee bags damaged" and [Description] <> "Employee bogo" and [Description] <> "Employee customer wait no internet" and [Description] <> "Employee dicount" and [Description] <> "Employee didcount" and [Description] <> "Employee dis." and [Description] <> "Employee disc" and [Description] <> "Employee disco7nt" and [Description] <> "Employee discoint" and [Description] <> "Employee discounr" and [Description] <> "Employee Discount" and [Description] <> "Employee discount" and [Description] <> "Employee Discount " and [Description] <> "Employee discount " and [Description] <> "employee discount " and [Description] <> "Employee discount  " and [Description] <> "Employee discount + return item" and [Description] <> "Employee discount approved by Jenna " and [Description] <> "Employee discount checking quality on Carmel’s per Sarah" and [Description] <> "Employee discount discount " and [Description] <> "Employee discount Dons nephew" and [Description] <> "Employee discount forgot to use receipt for 10%" and [Description] <> "Employee discount j" and [Description] <> "Employee discount l" and [Description] <> "Employee discount per m o" and [Description] <> "Employee discount per Sarah" and [Description] <> "Employee discount per Sarah " and [Description] <> "Employee discount t" and [Description] <> "Employee discounts " and [Description] <> "Employee discover" and [Description] <> "Employee discpunt" and [Description] <> "Employee display" and [Description] <> "Employee disvount" and [Description] <> "Employee doscount" and [Description] <> "Employee duscount" and [Description] <> "Employee expired product " and [Description] <> "Employee get out of system expiration " and [Description] <> "Employee p" and [Description] <> "Employee patt" and [Description] <> "Employee per ricky" and [Description] <> "Employee per Tyler short one on delivery " and [Description] <> "Employee phon" and [Description] <> "Employee Phon " and [Description] <> "Employee program" and [Description] <> "Employee trial" and [Description] <> "Employee Trial " and [Description] <> "Employee/boss" and [Description] <> "Employeee discount" and [Description] <> "Employees " and [Description] <> "Employees 1/8th each " and [Description] <> "Employees discount" and [Description] <> "Employees discount " and [Description] <> "Employment" and [Description] <> "Family" and [Description] <> "Family " and [Description] <> "First employee discount " and [Description] <> "Jason Williamson purchase employee discount " and [Description] <> "Moon Employee discount " and [Description] <> "PEmployee " and [Description] <> "Promo for employees" and [Description] <> "Sarah employee disc")),
    #"Added Index" = Table.AddIndexColumn(#"Filtered Rows1", "Index", 1, 0, Int64.Type),
    #"Renamed Columns1" = Table.RenameColumns(#"Added Index",{{"Index", "Discount Count"}}),
    #"Filtered Rows2" = Table.SelectRows(#"Renamed Columns1", each [Time] >= #time(8, 0, 0) and [Time] <= #time(23, 0, 0)),
    #"Filtered Rows3" = Table.SelectRows(#"Filtered Rows2", each true),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows3",{"Time", "Line Item", "Description", "Type", "Admin"}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Removed Columns1",{{"Report Start Date", type date}, {"Report End Date", type date}})
in
    #"Changed Type3"

 

[C Moore]

Can you create a table with all dates, append, and then remove duplicates?

 

[ExcelAtEverything]

I initially thought that there must be an easier way than that because the table would need to constantly change so that it always included all dates leading up to today's date. I figured there was likely an easier option within PQ, but if that's the answer then I can do that for sure. Thanks so much! ?