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
Super User
Super User

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"

 

 

 


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is beyond their comprehension!

DAX is simple, but NOT EASY!

View solution in original post

3 REPLIES 3
CNENFRNL
Super User
Super User

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"

 

 

 


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is beyond their comprehension!

DAX is simple, but NOT EASY!

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

 

 

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

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!