2
\$\begingroup\$

I have written a (relatively) generic function for interpolating values in power query (also useful for power bi and m code).

I am looking for any improvements or input, but especially:

  1. speed
  2. handle more inputs correctly (more generic)
  3. documentation / readability of code
(Input as table, xColumn as text, yColumn as text) =>
//Interpolates missing yColumn values based on nearest existing xColumn, yColumn pairs
let
    Buffer = Table.Buffer(Input),
    //index for joining calculations and preserving original order
    #"Added Main Index" = Table.AddIndexColumn(Buffer, "InterpolateMainIndex", 0, 1),
    #"Two Columns and Index" = Table.RemoveColumns(#"Added Main Index", List.Select(Table.ColumnNames(#"Added Main Index"), each _ <> xColumn and _ <> yColumn and _ <> "InterpolateMainIndex")),
    #"Remove Blanks" = Table.SelectRows(#"Two Columns and Index", each Record.Field(_, yColumn) <> null and Record.Field(_, yColumn) <> ""),
    //index for refering to next non-blank record
    #"Added Sub Index" = Table.AddIndexColumn(#"Remove Blanks", "InterpolateSubIndex", 0, 1),
    //m = (y2 - y1) / (x2 - x1)
    m = Table.AddColumn(#"Added Sub Index",
                        "m",
                        each    (Number.From(Record.Field(_, yColumn))-Number.From(Record.Field(#"Added Sub Index"{[InterpolateSubIndex]+1}, yColumn))) / 
                                (Number.From(Record.Field(_, xColumn))-Number.From(Record.Field(#"Added Sub Index"{[InterpolateSubIndex]+1}, xColumn))),
                        type number),
    //b = y - m * x
    b = Table.AddColumn(m, "b", each Record.Field(_, yColumn) - [#"m"] * Number.From(Record.Field(_, xColumn)), type number),
    //rename  or remove columns to allow full join
    #"Renamed Columns" = Table.RenameColumns(b,{{"InterpolateMainIndex", "InterpolateMainIndexCopy"}}),
    xColumnmb = Table.RemoveColumns(#"Renamed Columns",{yColumn, xColumn, "InterpolateSubIndex"}),
    Join = Table.Join(#"Added Main Index", "InterpolateMainIndex", xColumnmb, "InterpolateMainIndexCopy", JoinKind.FullOuter),
    //enforce orignal sorting
    #"Sorted by Main Index" = Table.Sort(Join,{{"InterpolateMainIndex", Order.Ascending}}),
    #"Filled Down mb" = Table.FillDown(#"Sorted by Main Index",{"m", "b"}),
    //y = m * x + b
    Interpolate = Table.ReplaceValue(#"Filled Down mb",null,each ([m] * Number.From(Record.Field(_, xColumn)) + [b]),Replacer.ReplaceValue,{yColumn}),
    //clean up
    #"Remove Temporary Columns" = Table.RemoveColumns(Interpolate,{"m", "b", "InterpolateMainIndex", "InterpolateMainIndexCopy"}),
    #"Restore Types" = Value.ReplaceType(#"Remove Temporary Columns", Value.Type(Input))
in
    #"Restore Types"
\$\endgroup\$
2
  • \$\begingroup\$ I am very thankful for this code as it's assisted me in setting up the function in Power Query and works well. How I could put an if statement when there is no input below and if it is blank below then interpolate the last 2 values above? Any help would be very appreciated. \$\endgroup\$ Commented Nov 2, 2023 at 2:36
  • \$\begingroup\$ @DavidSchindler if I'm understanding correctly you want to extrapolate rather than interpolate? I.e. your "x" which you want to generate a "y" for lies outside the max/min x for which you have data? \$\endgroup\$ Commented Nov 4, 2023 at 20:26

0

Reset to default

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.