Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I would like to fill in missing data values in timeseries by linear interpolation
My data
date | data value |
02-01-2019 | 127 |
06-01-2019 | 156 |
13-01-2019 | 178 |
16-01-2019 | 161 |
Result I would like to have
date | data value |
02-01-2019 | 127 |
03-01-2019 | 134 |
04-01-2019 | 142 |
05-01-2019 | 149 |
06-01-2019 | 156 |
07-01-2019 | 159 |
08-01-2019 | 162 |
09-01-2019 | 165 |
10-01-2019 | 169 |
11-01-2019 | 172 |
12-01-2019 | 175 |
13-01-2019 | 178 |
14-01-2019 | 172 |
15-01-2019 | 167 |
16-01-2019 | 161 |
Solved! Go to Solution.
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"
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! |
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! |
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