Fill in Records for Missing Dates

Power Query Data Setup
1

Hi
I am using PQ (in Excel. I have data for a number of non-sequential days. Each time data is recorded there are two records created holding Date, Code, Type, Value – one for Type A and One for Type B.
I need to create a table that takes the t_Initial table and then for each date in the range from the earliest date in t_Initial to the greatest Date in t_Initial there are the two records for each missing date for each combination of Code and Type. The value for the new records will be the value for the most recent record for the combination of Code and Type or if this is not available then 0.
I am only interested in Dates in t_output that are for workdays (Mon-Fri)

I have attached a workbook showing the desired output.

I have tried to Generate list of sequential dates from the lowest to the greatest date in t_Intial but have not succeeded in this.

I think the answer to my problem will involve Group By in Power Query.
I am keen for any ideas.

Thank You

Allister

Fil In Records.xlsx (19.8 KB)

2

Hi @AllisterB ,

You can check Enteprise DNA Data Mentor Tools Advisor :

For Date Dimension - how to create you can check Melisa’s Date M function:

Hope it helps.