listtypespowerquerylinear-interpolation

Power Query M: List.Generate condition doesn't work between values of 4 and 16

I have a table like this:

v p
3 89
3.5 178
4 328
4.5 522
5 758
5.5 1040

v goes from 3 to 24 in steps of 0.5 (v = List.Numbers(3, 43, 0.5)).

p = {89, 178, 328, 522, 758, 1040, 1376, 1771, 2230, 2758, 3351, 3988, 4617, 5166, 5584, 5862, 6028, 6117, 6161, 6183, 6192, 6192, 6192, 6192, 6192, 6192, 6192, 6192, 6192, 6192, 6192, 6192, 6192, 6192, 6192, 6192, 5956, 5832, 5708, 5584, 5460, 5336, 5212}

I want to interpolate the values for which I found a solution here. I changed the code, because I need one decimal digit.

let
    Source = Excel.CurrentWorkbook(){[Name="LK"]}[Content],
    //Add two columns which are the v and p columns offset by one
    //It is faster to subtract this way than by adding an Index column
    offset= 
        Table.FromColumns(
            Table.ToColumns(Source) 
                & {List.RemoveFirstN(Source[v]) & {null}}
                & {(List.RemoveFirstN(Source[p])) & {null}},
            type table[v=Decimal.Type, p=Decimal.Type, sv=Decimal.Type, sp=Decimal.Type]),
    
    // Add a column with a list of the interest rates for each data interpolated between the segments
    #"p_interpol" = Table.AddColumn(offset, "p_interpol", each let
            sv_val=[sv], 
            p_increment = ([sp]-[p])/(([sv]-[v])*10),
            Lists= List.Generate(
                ()=>[v_val=[v],p_val=[p]],
                each [v_val]< sv_val,
                each [v_val=[v_val]+0.1, p_val = [p_val]+p_increment],
                each [p_val])
        in Lists),
    
    // add another column with a list of days corresponding to the interest rates
    #"v_interpol" = Table.AddColumn(#"p_interpol", "v_interpol", each List.Numbers([v], ([sv]-[v])/0.1, 0.1)),
    
    //remove the last row as it will have an error
    remErrRow = Table.RemoveLastN(#"v_interpol",1),

    //create the new table which has the rates for every duration
    LK_interpol = Table.FromColumns(
                        {List.Combine(remErrRow[v_interpol]),List.Combine(remErrRow[p_interpol])},
                        type table[v=Decimal.Type, p=Decimal.Type])
in
    LK_interpol

As Output I get this:

v p
3 89
3.1 106.8
3.2 124.6
3.3 142.4
3.4 160.2
3.5 178
3.6 208
3.7 238
3.8 268
3.9 298
4 328
4.1 366.8
4.2 405.6
4.3 444.4
4.4 483.2
4.5 522
4.6 522
4.7 569.2

The interpolation works until v = 4. It seems like the condition in the List.Generate function doesn't work anymore when v = 4, because the generated list contains six instead of five elements. This leads to doubled values (in the example output 522 for v = 4.5). The same problem occurs for the following rows until v = 16. From v = 16 on the interpolation works again.

I also tested what happens if I add rows before v = 3 or if I change the values of p. The result stays the same: List.Generate generates me a list with six elements if v >= 4 and v < 16, otherwise the generated list has five elements.

What could be the reason? Maybe something with the datatype?

Solution

  • Seems like it should work, not quite sure why it doesnt (perhaps rounding in smallest decimal point) but you can fix it with

     #"p_interpol" = Table.AddColumn(offset, "p_interpol", each let
        sv_val=[sv], 
        p_increment = ([sp]-[p])/(([sv]-[v])*10),
        Lists= List.Generate(
            ()=>[v_val=[v],p_val=[p]],
            each [v_val]< sv_val,
            each [v_val=[v_val]+0.1, p_val = [p_val]+p_increment],
           each [p_val]),
        Clip=List.FirstN(Lists,([sv]-[v])*10)
    in Clip),