cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
retgeav
Regular Visitor

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

I would like to fill in missing data values in timeseries by linear interpolation

 

My data

datedata value
02-01-2019127
06-01-2019156
13-01-2019178
16-01-2019161

 

Result I would like to have 

datedata value
02-01-2019127
03-01-2019134
04-01-2019142
05-01-2019149
06-01-2019156
07-01-2019159
08-01-2019162
09-01-2019165
10-01-2019169
11-01-2019172
12-01-2019175
13-01-2019178
14-01-2019172
15-01-2019167
16-01-2019161
3 ACCEPTED SOLUTIONS
Jimmy801
Community Champion
Community Champion

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"

 

Jimmy801_0-1603184186959.png

 

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

 

 

View solution in original post

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 

 

SU18_powerbi_badge

View solution in original post

CNENFRNL
Community Champion
Community Champion

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"

 

 

 

View solution in original post

3 REPLIES 3
CNENFRNL
Community Champion
Community Champion

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"

 

 

 

View solution in original post

Jimmy801
Community Champion
Community Champion

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"

 

Jimmy801_0-1603184186959.png

 

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

 

 

View solution in original post

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 

 

SU18_powerbi_badge

View solution in original post

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors