Insert Missing Days & Copy Earlier Day data to missing days

Question

I have a table Summary, with the following columns - Date Bank Name MtM RW 3% Options CCS CCS 6% Below is sample table -

Date	Bank Name	MtM	RW 3%	Options	CCS	CCS 6%
29 Dec 2023	Citibank	300	45	0	375	0
30 Dec 2023	HSBC	175	225	0	0	200
02 Jan 2023	Citibank	475	150	28	0	220

I would like to copy the data of 30th Dec, 2023 for 31st December 2023 and 1st Jan 2024 with the same values as of 30th Dec 2023.

How can i do this in Power BI.

Appreciate any help !

Tried to manually load the file and then append, but its messing up the table structure.

Answer 1

PowerQuery

Try the following steps in PowerQuery for your table/query.

let
  Source = YOUR_SOURCE,
  #"Changed column type" = Table.TransformColumnTypes(Source, {{"Date", type date}}),
  #"Sorted rows" = Table.Sort(#"Changed column type", {{"Date", Order.Ascending}}),
  #"Added index" = Table.AddIndexColumn(#"Sorted rows", "Index", 0, 1, Int64.Type),
  #"Added custom" = Table.AddColumn(#"Added index", "Custom", each 
  let
    thisDate = [Date],
    nextDate = try Date.AddDays(#"Added index"{[Index] +1}[Date], -1) otherwise [Date],
    listDate = { Number.From(thisDate)..Number.From(nextDate) }
  in
    listDate
  ),
  #"Expanded Custom" = Table.ExpandListColumn(#"Added custom", "Custom"),
  #"Changed column type 1" = Table.TransformColumnTypes(#"Expanded Custom", {{"Custom", type date}}),
  #"Removed columns" = Table.RemoveColumns(#"Changed column type 1", {"Date", "Index"}),
  #"Renamed columns" = Table.RenameColumns(#"Removed columns", {{"Custom", "Date"}})
in
  #"Renamed columns"

---

DAX

NewTable = 
  var dates = 
    SELECTCOLUMNS(
      CALENDAR( MIN('YourTable'[Date]), MAX('YourTable'[Date])),
      "DateNew", [Date]
    )
  var missingDate = EXCEPT(dates, DISTINCT('YourTable'[Date]))
  var missingRows = GENERATE(missingDate, TOPN(1, FILTER('YourTable', [Date] < [DateNew]), [Date], DESC))

  return UNION(
    'YourTable',
    SELECTCOLUMNS(
      missingRows,
      "Date", [DateNew],
      "Bank Name", [Bank Name],
      "MtM", [MtM],
      "RW 3%", [RW 3%],
      "Options", [Options],
      "CCS", [CCS],
      "CCS 6%", [CCS 6%]
    )
  )