Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Interpolate missing data with group by column

Hello All,

 

I would like to have blank values to be filled with interpolated values as per the dates and name column values.

Sample data:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jVg7blwxDLxK4DoRxD+ZLgiQJilSBGkM3/8a4VvEi/UjtXqGXSzI0UokZzTy6+sLyJeJX3AifZrz65wvn1944o/vYDZ+/81PL2+fP2bBpSy8lEWXsvhSllzK0ktZdinLL2XFlSy4VHu4VHtoa080UJWnABRA2wbCAQAJICuAtiMEA12ExakA2ubkN0wyDjcpgLZPyGNOw/zjAmhbllsyJEefXgBt97JKk5hMIAqgbeRRVkyEkp4BeKmn2PY0S4MqSBRlH9i2F3WIehZTK+AJy3TDfpTBCCwmXADtxnMfkXOGzFIAy7kMC1CGAujnkgZD9rSm91OZ6aSiIF4A/VTSuE1AyW5H8rZ9ZeeogH4kZwJ4ekwqgHYk0YcTmBBaAbQjmQBDxThn9xqDMSxYaGqpTy83xwHQVKQeYKE8c5CEaFBpWa88SXOfIIqznLhXHrSBM/IIVA/d9xhGdszEpQL6NicfCdgI6hn6NuOQg44etay98iQgx4JTrwqgVx4cSXjI33KGXnmebGkpQgqU4ll21GvQkW8HdwALYHXF6I36/v8ItpGj4whJHE36F8CTu9E2GlSyVrsVOAqIXABPLIrt9CaLgIEUpgXwxK3YRmdSBbKyxkmkAlgNYCa7kdRmrAZQJPLn/XK1jdSUrP4GSUV1nDS93GTvQ/Hrz/ef355402UcN3HaxHkTl01cN3HbxH0Tj+dx2NQPNvWDU/2Uh0GOAFX/dqqk5pCn+VRHXJnJe6oPUASgxoKd6quRpsemp4isDOR7qkimMpNrNadWVgXPCWy+f1N/ONXfjmtmQop08dy4aQVuWoHnVszctPiUyhpsp7o6v3upbFDIjYMrz3dPjeGRyhVYzVXLtWru7rXC4wDAKOXy6GlXfdzDUkpAItUD9Qysnu1eVsv3AIajVv/rZcQNOK1p6Mqn3VNT4mhCesalSXugGAWpe6nKmayqYybHwr0ueh6WfCykhyOaxbSeiZu8MQdwtKUXu696PLaQj5OtXNgDxfLRl+tiqdWZuDbTDMrthbVyXg90A6ZsVbNXL6m5Zl52tVlnEq/M1TLeMrd6p2X8TAw43DBCWrGVSfq4VPVEy3i7leqDlvGWmNXwLOMtG6uzeRC5fMtHOFQHdm6wjZiRbKim6izSMtJ6sd//C1FtzGp/ZwqyH69lhODc39s/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Datetime = _t, Name = _t, value = _t]),
    #"Added Suffix" = Table.TransformColumns(Source, {{"Datetime", each _ & ":00", type text}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Added Suffix", {{"Datetime", type datetime}}, "en-NA"),
    #"Changed Type" = Table.TransformColumnTypes(#"Changed Type with Locale",{{"value", type number}})
in
    #"Changed Type"

 

 

I have a column which has tag names and here i have sample data of two tags for two days.

The columns i have here as Datetime, NAME and value columns.

for each name i want to fill the blank values using interpolation by Name column of the datetime.

I found multiple blogs and community questions on interpolation most of them using dax but not with power query.

https://community.powerbi.com/t5/Community-Blog/Linear-Interpolation-with-Power-BI/ba-p/341202

https://stackoverflow.com/questions/68416307/power-bi-power-query-m-linear-interpolation-over-time-s...

 

 

Can anyone please help me on calculating interpolation values.

 

Thanks,

Mohan V.

Data Protection
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

11 Replies

  • ronrsnfld's avatar
    ronrsnfld
    Super User
    3 years ago

    What kind of interpolation? Linear or something else?

    Or just a straight line interpolation between the points surrounding the missing values?

    If the latter, how do you want to extend the values for the beginning/end where there is no boundary value?

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      ronrsnfld straight line interpolation between the points surrounding the missing values is what I'm looking for.

      • ronrsnfld's avatar
        ronrsnfld
        Super User
        3 years ago

        For the straight line interpretation solution, how will you handle the situation where there is no starting value as at the top of your table?

    • ronrsnfld's avatar
      ronrsnfld
      Super User
      3 years ago

      Out of town now. But have a method for straight line Interpolation of missing data points ignoring missing data at very start or end. Will post later this week.

    • ronrsnfld's avatar
      ronrsnfld
      Super User
      3 years ago

      Unable to download your file.

       

      Below is code that

      • interpolates missing values using a "straight-line" between the entries surrounding the missing values
      • If the sequence starts with a null or series of nulls, those will be ignored.
      • If the sequence ends with a null or series of nulls, those will be ignored.

      Custom function to do the interpolateion

      Rename a blank query => fnStraightLineInterpolation

       

      (t as table)=>

let 
    #"Grouped Rows" = Table.Group(t, {"value"}, {
        {"Null Count", each if [value]{0}=null then List.Count([value]) else 0, Int64.Type}},
        GroupKind.Local),
    
    #"Shifted" = Table.FromColumns(
            Table.ToColumns(#"Grouped Rows")
            & {List.RemoveFirstN(#"Grouped Rows"[value] & {null})}
            & {{null} & List.RemoveLastN(#"Grouped Rows"[value])},
            type table[value=number,Null Count=Int64.Type, after Null=number, before Null=number]),

    #"Added Custom" = Table.AddColumn(Shifted, "Interpolation", each 
        if [Null Count] = 0 then {[value]}
            else let 
                    increment = ([after Null] - [before Null]) / ([Null Count]+1),
                    values = if increment = null
                                then List.Repeat({null},[Null Count])
                                else List.Numbers([before Null] + increment, [Null Count], increment)
                in 
                    values, type list),

//Merge back with Datetime and Name columns
    #"Result" = Table.FromColumns(
                    Table.ToColumns(Table.SelectColumns(t,{"Datetime","Name"}))
                    & {List.Combine(#"Added Custom"[Interpolation])},
                type table[Datetime=datetime, Name=text, Value=number])

in
    #"Result"

       

       

      Main Query

       

      let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jVg7blwxDLxK4DoRxD+ZLgiQJilSBGkM3/8a4VvEi/UjtXqGXSzI0UokZzTy6+sLyJeJX3AifZrz65wvn1944o/vYDZ+/81PL2+fP2bBpSy8lEWXsvhSllzK0ktZdinLL2XFlSy4VHu4VHtoa080UJWnABRA2wbCAQAJICuAtiMEA12ExakA2ubkN0wyDjcpgLZPyGNOw/zjAmhbllsyJEefXgBt97JKk5hMIAqgbeRRVkyEkp4BeKmn2PY0S4MqSBRlH9i2F3WIehZTK+AJy3TDfpTBCCwmXADtxnMfkXOGzFIAy7kMC1CGAujnkgZD9rSm91OZ6aSiIF4A/VTSuE1AyW5H8rZ9ZeeogH4kZwJ4ekwqgHYk0YcTmBBaAbQjmQBDxThn9xqDMSxYaGqpTy83xwHQVKQeYKE8c5CEaFBpWa88SXOfIIqznLhXHrSBM/IIVA/d9xhGdszEpQL6NicfCdgI6hn6NuOQg44etay98iQgx4JTrwqgVx4cSXjI33KGXnmebGkpQgqU4ll21GvQkW8HdwALYHXF6I36/v8ItpGj4whJHE36F8CTu9E2GlSyVrsVOAqIXABPLIrt9CaLgIEUpgXwxK3YRmdSBbKyxkmkAlgNYCa7kdRmrAZQJPLn/XK1jdSUrP4GSUV1nDS93GTvQ/Hrz/ef355402UcN3HaxHkTl01cN3HbxH0Tj+dx2NQPNvWDU/2Uh0GOAFX/dqqk5pCn+VRHXJnJe6oPUASgxoKd6quRpsemp4isDOR7qkimMpNrNadWVgXPCWy+f1N/ONXfjmtmQop08dy4aQVuWoHnVszctPiUyhpsp7o6v3upbFDIjYMrz3dPjeGRyhVYzVXLtWru7rXC4wDAKOXy6GlXfdzDUkpAItUD9Qysnu1eVsv3AIajVv/rZcQNOK1p6Mqn3VNT4mhCesalSXugGAWpe6nKmayqYybHwr0ueh6WfCykhyOaxbSeiZu8MQdwtKUXu696PLaQj5OtXNgDxfLRl+tiqdWZuDbTDMrthbVyXg90A6ZsVbNXL6m5Zl52tVlnEq/M1TLeMrd6p2X8TAw43DBCWrGVSfq4VPVEy3i7leqDlvGWmNXwLOMtG6uzeRC5fMtHOFQHdm6wjZiRbKim6izSMtJ6sd//C1FtzGp/ZwqyH69lhODc39s/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Datetime = _t, Name = _t, value = _t]),
    #"Added Suffix" = Table.TransformColumns(Source, {{"Datetime", each _ & ":00", type text}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Added Suffix", {{"Datetime", type datetime}}, "en-NA"),
    #"Changed Type" = Table.TransformColumnTypes(#"Changed Type with Locale",{{"value", type number}}),
   
//Group by Name
//  then interpolate each subgroup
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Name"}, {
        {"All", each fnStraightLineInterpolation(_)}}),

//Remove unneeded column and Expand the table
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Name"}),
    #"Expanded All" = Table.ExpandTableColumn(#"Removed Columns", "All", {"Datetime", "Name", "Value"}, {"Datetime", "Name", "Value"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded All",{{"Datetime", type datetime}, {"Name", type text}, {"Value", type number}})
        
in
    #"Changed Type1"

       

      Part of output after interpolation

      Graphic output before and after processing