Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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"

 

 

 


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 still way beyond their comprehension!

DAX is simple, but NOT EASY!

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"

 

 

 


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 still way 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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors