Dynamic Date fill with pivot

[tokki]

I am looking for help determining the best solution for what I am looking to achieve.

Given: A table of projects with contract date and contact value.

Project Date Value
Project ABC 1/1/2020 10,000
Project ABC 1/1/2021 11,000
Project DEF 3/1/2020 12,000
Project DEF 1/1/2021 14,000
Project GHI 7/1/2020 9,000
Project GHI 1/1/2021 17,000

Using excel power query, I am looking to dynamically create a table with all possible dates between the first date and the current date and display associated values at a given date (first day of month)

DATE ABC DEF GHI
1/1/20 10,000
2/1/20 10,000
3/1/20 10,000 12,000
4/1/20 10,000 12,000
5/1/20 10,000 12,000
6/1/20 10,000 12,000
7/1/20 10,000 12,000 9,000
8/1/20 10,000 12,000 9,000
9/1/20 10,000 12,000 9,000
10/1/20 10,000 12,000 9,000
11/1/20 10,000 12,000 9,000
12/1/20 10,000 12,000 9,000
1/1/21 11,000 14,000 9,000
2/1/21 11,000 14,000 9,000
3/1/21 11,000 14,000 9,000
4/1/21 11,000 14,000 9,000

Thoughts on the best way to achieve this?

 

[Alex Blakenburg]

Does this look like the result you are after (since it has every day listed it was too big for XL2BB and the image only shows the rows where the values change) ?
The only way I could do it was to create a calendar query (starting at 01 Jan 2020 and I ran it through to 31 Dec 2021).
I then pivoted the project data, linked it (Merged) it with the calendar query so all dates in the calendar where in the output, then did some fill down and then replace nulls with 0.

I can go into more detail if this sounds like something that will work for you and you can give me an indication on what parts you already know how to do.

Calendar query.
730 is the number of days to extend the calendar to.

let
Source = List.Dates(#date(2020, 1, 1), 730, #duration(1, 0, 0, 0)),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "CalendarDate"}})
in
#"Renamed Columns"

 

[tokki]

Yes, I believe that looks like what I am after.

 

[Alex Blakenburg]

OK it got a little more involved, since the Merge Query hard coded the Project Name Columns.
If you can live with the names I have used you can follow the process is below.
If you need any of the names changed, the below won't work since each step refers to the previous step by name.

For the query steps the process is.

• Create a blank query
  From Excel - Data > Get Data > From Other Sources > Blank Query
  From PQ - Right click the query navigation pane > New Query > Other Sources > Blank Query
• Go to View > Advanced Editor
• Replace what is in the Editor with the code provided
• Make sure you rename the query to the name I have used before moving on to repeat the process with the next query.
• Close & Load is as per the steps below

Steps:-

1. Convert your initial data into an Excel Table and call it Project_Data.
   Let me know if you don't know how to do this or if you can't live with the name
   
   
2. Query 1 - Project_Data_Pivoted
   Loads Project_Data table, Pivots it and renames the Date field to Date Start
   Close & Load To > Only Create Connection
   
   

   let
       Source = Excel.CurrentWorkbook(){[Name="Project_Data"]}[Content],
       #"Renamed Columns" = Table.RenameColumns(Source,{{"Date", "Date Start"}}),
       #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date Start", type date}}),
       #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Project]), "Project", "Value", List.Sum)
   in
       #"Pivoted Column"

   
   
3. Query 2 - Calendar
   Creates a List of Dates - currently from 1 Jan 2020 for 1,096 days being to 31 Dec 2022.
   This is the upper limit of the output, you can set a lower limit by putting in date criteria in the final query.
   Named the date column CalendarDate
   Close & Load To > Only Create Connection
   
   

   let
       Source = List.Dates(#date(2020, 1, 1), 1096, #duration(1, 0, 0, 0)),
       #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
       #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "CalendarDate"}})
   in
       #"Renamed Columns"

   
   
4. Query 3 - CalendarProjectMerge
   This joins the Calendar Query with the Project Data.
   It has some customized M code in it to accommodate a variable number of projects.
   (the default is to use column names prevents it from picking up additional projects as they are added)
   Close & Load To > select where you want the output to go.
   
   

   let
       Source = Table.NestedJoin(Calendar, {"CalendarDate"}, Project_Data_Pivoted, {"Date Start"}, "Project_Data_Pivoted", JoinKind.LeftOuter),
       #"Changed Type Date" = Table.TransformColumnTypes(Source,{{"CalendarDate", type date}}),
       ColumnNames = Table.ColumnNames(Table.Combine(#"Changed Type Date"[Project_Data_Pivoted])),
       ExpandedColumns = Table.ExpandTableColumn(#"Changed Type Date", "Project_Data_Pivoted", ColumnNames, ColumnNames),
       #"Sorted Rows" = Table.Sort(ExpandedColumns,{{"CalendarDate", Order.Ascending}}),
       #"Filled Down Custom" = Table.FillDown(#"Sorted Rows",ColumnNames),
       #"Filtered Rows Null Date" = Table.SelectRows(#"Filled Down Custom", each ([Date Start] <> null)),
       #"Replaced Nulls Custom" = Table.ReplaceValue(#"Filtered Rows Null Date",null,0,Replacer.ReplaceValue,ColumnNames)
   in
       #"Replaced Nulls Custom"

​