Interpolate using Power Query

Power Query M Code Showcase
1

image

Hi,
Is there a way to get values in blank cells using the power query?
condition - Columns and rows are dynamic. Column ‘A’ is static.
Column “A” will be always the starting point. “B” & “C” may be blank.
looking for a solution using the Power M language.
Thanks

2

What is the logic for determining the values that should go into the blank cells?

3

Interpolation or Forecast method. This is possible in excel but looking for power query method.

4

What the logic in Excel that you are using?

5

Using formula “Forecast.Linear”

6

Answer:

Hi @TS1,

Copy this M code into a new blank query.

let
    Source = Table.FromColumns(
        {
            {1,3,4,5,6.6,8.2},
            {21, 4,7,9,null, null},
            {33, 6,8,11,null, null}
        },
        type table[A=number, B=number, C=number]
    ),
    fx = ( tbl as table) =>
    let
        colNames = Table.ColumnNames(tbl),
        calc = List.Accumulate(
            {0..List.Count(colNames)-1},
            tbl,
            (s, i)=> if List.PositionOf( colNames, colNames{i} ) >0 
                then let
                basePos = List.PositionOf( colNames, colNames{i-1} ),
                intPos = List.PositionOf( colNames, colNames{i} ),
                toCols = Table.ToColumns( s ),
                l = List.Buffer( toCols{basePos} ),
                r = List.Buffer( toCols{intPos} ),
                int = List.Generate(
                    ()=> [a = l, c = List.PositionOf(r, null), b = List.ReplaceRange(r, c, 1, { r{c-2}+(l{c}-l{c-2})*(r{c-1}-r{c-2})/(l{c-1}-l{c-2}) } ) ],
                    each [c] >= 2,
                    each [a = [a], c = List.PositionOf([b], null), b = List.ReplaceRange([b], c, 1, { [b]{c-2}+([a]{c}-[a]{c-2})*([b]{c-1}-[b]{c-2})/([a]{c-1}-[a]{c-2}) } ) ],
                    each [b]
                ),
                t = Value.ReplaceType( Table.FromColumns( List.ReplaceRange( toCols, intPos, 1, { List.Last(int)}) ), Value.Type(tbl))
                in 
                    t
                else s
        )
    in
        calc,
    interpolate = fx(Source)
in
    interpolate

.
It’s a bit verbose but this custom function seems to do the trick. Amend to your needs.
image

I hope this is helpful.