How to fill in missing data values in timeseries by linear interpolation

Hello retgeav 

this requires quite a lot of steps and for sure there are more possibilities out. Here the M-code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDSNTDUNTIwtFTSUTI0MleK1QEKmiELmpqBBQ2NkQXNLSCCKCrNDJViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [date = _t, #"data value" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type date}, {"data value", Int64.Type}}, "en-DE"),
    ListMissingDates = Table.RenameColumns(Table.FromList(List.Difference(List.Dates(List.Min(#"Changed Type"[date]),Duration.TotalDays(List.Max(#"Changed Type"[date])-List.Min(#"Changed Type"[date])), #duration(1,0,0,0) ), #"Changed Type"[date]), Splitter.SplitByNothing(),null, null, ExtraValues.Error), {{"Column1", "date"}}),
    Combine = Table.Combine({#"Changed Type", ListMissingDates}),
    #"Sorted Rows" = Table.Sort(Combine,{{"date", Order.Ascending}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Sorted Rows", "data value", "GroupColumn"),
    #"Duplicated Column1" = Table.DuplicateColumn(#"Duplicated Column", "data value", "MaxValue"),
    #"Filled Down" = Table.FillDown(#"Duplicated Column1",{"GroupColumn"}),
    #"Filled Up" = Table.FillUp(#"Filled Down",{"MaxValue"}),
    #"Grouped Rows" = Table.Group(#"Filled Up", {"GroupColumn"}, {{"AllRows", each _, type table [date=date, data value=number, GroupColumn=number, MaxValue=number]}}),
    TransformGroupedTable = Table.TransformColumns
    (
        #"Grouped Rows",
        {
            {
                "AllRows",
                (tbl)=> 
                let 
                    Index= Table.AddIndexColumn(tbl,"Index",0,1), 
                    CalculateIncrement = (tbl[MaxValue]{1}-tbl[MaxValue]{0})/Table.RowCount(tbl),
                    AddColumn = Table.AddColumn
                    (
                        Index,
                        "New data value", 
                        each try [GroupColumn]+([Index]*CalculateIncrement) otherwise [GroupColumn]
                    )
                in 
                    AddColumn
            }
        }
    ),
    #"Removed Columns" = Table.RemoveColumns(TransformGroupedTable,{"GroupColumn"}),
    #"Expanded AllRows" = Table.ExpandTableColumn(#"Removed Columns", "AllRows", {"date", "New data value"}, {"date", "New data value"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded AllRows",{{"date", type date}, {"New data value", type number}})
in
    #"Changed Type1"

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

Hi retgeav 

This would be simpler in DAX. If you need it in PQ, paste the following code in a blank query to see the steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDSNTDUNTIwtFTSUTI0MleK1QEKmiELmpqBBQ2NkQXNLSCCKCrNDJViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [date = _t, #"data value" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type date}, {"data value", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"date", Order.Ascending}}),
    #"Added Custom" = Table.AddColumn(#"Sorted Rows", "Custom", each try let currentValue_= Number.From([data value]), numSteps_ = Number.From(List.Select(#"Sorted Rows"[date],(inner)=>inner> [date]){0}-[date]), nextValue_ = Table.SelectRows(#"Sorted Rows",(inner)=>inner[date]> [date])[data value]{0}  in Table.FromColumns({List.Numbers(Number.From([date]),numSteps_), List.Numbers(currentValue_, numSteps_, (nextValue_-currentValue_)/numSteps_)}) otherwise Table.FromColumns({{Number.From([date])},{[data value]}})),
    #"Expanded Custom.1" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Column1", "Column2"}, {"Column1", "Column2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom.1",{{"Column1", type date}, {"Column2", type number}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"date", "data value"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column1", "date"}, {"Column2", "data value"}})
in
    #"Renamed Columns"

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

Hi, retgeav , you may try this, merely one single function List.Generate() gets the job done.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDSNTDUNTIwtFTSUTI0MleK1QEKmiELmpqBBQ2NkQXNLSCCKCrNDJViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [date = _t, #"data value" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type date}, {"data value", Int64.Type}}, "en-GB"),

    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added to Column" = Table.TransformColumns(
        #"Added Index",
        {{"Index", (row) => 
            List.Generate(
                () => [date=#"Added Index"[date]{row},
                    e=try #"Added Index"[date]{row+1} otherwise Date.AddDays(date,1),
                    incr=(#"Added Index"[data value]{row+1}-#"Added Index"[data value]{row})/Number.From(e-date),
                    value=#"Added Index"[data value]{row}],
                each [date] < [e],
                each [date=Date.AddDays([date],1), e=[e], value=[value]+incr, incr=[incr]],
                each [[date], [value]]
            )
        }}
    ),
    #"Removed Columns" = Table.RemoveColumns(#"Added to Column",{"date", "data value"}),
    #"Expanded Index" = Table.ExpandTableColumn(#"Removed Columns", "Index", {"date", "value"}, {"date", "value"})
in
    #"Expanded Index"