How to add rows with missing dates

Hi Anonymous , 

After loading the table in the Power Query editor and adding a new query, use the following M code to reference the source table to create a new table that meets the requirements.

let
  StartDate = List.Min(Table[Date from original report]),
  EndDate = List.Max(Table[Date from original report]),
  DateList = List.Dates(StartDate, Duration.Days(EndDate - StartDate) + 1, #duration(1, 0, 0, 0)),
  DateTable = Table.TransformColumnTypes(
    Table.FromList(
      DateList,
      Splitter.SplitByNothing(),
      {"Date from original report"},
      null,
      ExtraValues.Error
    ),
    {{"Date from original report", type date}}
  ),
  #"Merged Queries" = Table.NestedJoin(
    DateTable,
    {"Date from original report"},
    Table,
    {"Date from original report"},
    "Table",
    JoinKind.LeftOuter
  ),
  #"Expanded Table" = Table.Sort(
    Table.ExpandTableColumn(#"Merged Queries", "Table", {"ExRate"}, {"ExRate"}),
    {{"Date from original report", Order.Ascending}}
  ),
  #"Filled Down" = Table.FillDown(#"Expanded Table", {"ExRate"}),
  #"Added Custom" = Table.AddColumn(
    #"Filled Down",
    "Date from final report",
    each [Date from original report] + #duration(1, 0, 0, 0)
  ),
  #"Changed Type" = Table.TransformColumnTypes(
    #"Added Custom",
    {{"Date from final report", type date}}
  ),
  #"Reordered Columns" = Table.ReorderColumns(
    #"Changed Type",
    {"Date from original report", "Date from final report", "ExRate"}
  )
in
  #"Reordered Columns"

Then merge the date table with the source data table using the left join.

Select the 'ExRate' column and use the fill down.

Add a custom column that shows the date moved forward one day.

Please see the attached pbix for reference.

Best Regards,
Dengliang Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.