Fill or interpolate missing dates in a matrix in PowerBI

Question

Colleagues in my organisation track serviceability of their assets on a daily basis so our bosses can get an idea of what they have to use and how much of an umbrella they need to put up for any day.

My colleagues use a matrix (example of the matrix), for ease they have the top row as the first date of the week they started on, the second row (not shown) is the day name they're on.

In PowerBI its easy enough to transpose, and be left with a column of dates with blanks. Filling the missing dates is proving difficult.

I've been able to make little isolated lists of dates but I haven't been able to join them back to the original information.

Excel can interpolate these, but PowerBI just wants to copy down the value from above. Additional caveat; I am restricted in not being able to use Python or R for now.

Here's another example of the table after I've transposed it to carry out the filling I want to do:

Column1                       Column2    Column3  Column4 Column5
null                         Truck      Truck    Car     Car
null                         Fred       Tim      Alice   Beatie
Week beginning 8th Aug 2019  Good       Bad      Good    Good
null                         Bad        Bad      Good    Good
null                         Bad        Good     Good    Bad
null                         Good       Good     Good    Bad
null                         Good       Good     Bad     Bad
null                         Good       Bad      Bad     Good
null                         Bad        Good     Bad     Good
Week beginning 15th Aug 2019 Bad        Bad      Good    Bad
...                          ...        ...      ...     ...

What I would like to end up with is:

Column1       Column2    Column3  Column4 Column5
null         Truck      Truck    Car     Car
null         Fred       Tim      Alice   Beatie
8/Aug/2019   Good       Bad      Good    Good
9/Aug/2019   Bad        Bad      Good    Good
10/Aug/2019  Bad        Good     Good    Bad
11/Aug/2019  Good       Good     Good    Bad
12/Aug/2019  Good       Good     Bad     Bad
13/Aug/2019  Good       Bad      Bad     Good
14/Aug/2019  Bad        Good     Bad     Good
15/Aug/2019  Bad        Bad      Good    Bad
...          ...        ...      ...     ...

Thanks in advance!

Answer 1

You can do this with the List.Dates function.

As an example, given the data:

The following M Code will convert this to a list of consecutive dates:

let
    Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type any}}),

        firstDate = DateTime.Date(#"Changed Type"[Column1]{1}),
        lastDate = DateTime.Date(List.Last(#"Changed Type"[Column1])),
        days = Number.From(lastDate - firstDate) +1,

    allDates = List.Dates(firstDate,days,#duration(1,0,0,0)),
    withText = List.Combine({{#"Changed Type"[Column1]{0}},allDates}),
    #"Converted to Table" = Table.FromList(withText, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
        
in
    #"Converted to Table"