Fill In Missing Dates in Power Query

ExcelAtEverything

Active Member
Joined
Jan 30, 2021
Messages
322
Office Version
  1. 2019
Platform
  1. Windows
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.

Power Query:
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"
 

Attachments

  • Capture.PNG
    Capture.PNG
    10.8 KB · Views: 10

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

C Moore

Well-known Member
Joined
Jan 17, 2014
Messages
581
Office Version
  1. 365
Platform
  1. Windows
Can you create a table with all dates, append, and then remove duplicates?
 
Solution

ExcelAtEverything

Active Member
Joined
Jan 30, 2021
Messages
322
Office Version
  1. 2019
Platform
  1. Windows
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! 🙏
 

Forum statistics

Threads
1,141,613
Messages
5,707,394
Members
421,508
Latest member
Jalayne

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top