Need Help With creating an interpolation function and then calculating stresses

Hi again Anonymous

In the attached PBIX, I've constructed some queries demonstrating how you could set this up 🙂

1. I started with tables similar to what you had posted: Markov, Stress_Transfer_Function, and Bending_Moment.
2. I created a function called fn_Generate_Interpolation_Function that takes two arguments (a list of x-values and a list of y-values) and outputs an interpolation function. Code is posted further down.
   In the case of your 11 pairs of x/y values, this function
   
   1. Converts the list of 11 x/y pairs to a list of 10 records, each containing x_min, x_max, y_min, slope. This is called Intervals in the code.
   2. Creates a function called InterpolationFunction in the code, which takes a single argument x_input, then determines which interval it sits in and calculates y_output.
   3. If x is lower or higher than the values from the original list of x-values, the top or bottom intervals are assumed to extend to +/- infinity.
3. Then I created an intermediate query called Interpolation_Functions.
   1. Starts with Stress_Transfer_Function
   2. Joins Bending_Moment based on TowerModelId.
   3. Expands the Value columns from Bending_Moment, changing the names to Bending_Moment.Value1 etc.
   4. Merges the Value column values into a list column called y_values.
   5. Merges the Bending_Moment.Value column values into a list column called x_values.
   6. Adds a column which applies fn_Generate_Interpolation_Function to x_values & y_values.
4. Then I joined Interpolation_Functions into Markov and expanded the InterpolationFunction column.
5. Lastly, apply the InterpolationFunction column (which contains a function) to the Max/Min Desired Bending Moment columns to produce the corresponding Stress columns. Tidy up by removing the InterpolationFunction column.

Based on the data I tested with, it seems to be working correctly.

Oh, here's the code for fn_Generate_Interpolation_Function (also in the PBIX):

( x as list, y as list ) =>
let
    // x & y lists for testing
    //x = {-9500,-8000,-6500,-5000,-3500,-2000,-500,1000,2500,4000,6000},
    //y = {-163.48,-128.18,-92.6,-57.46,-22.02,13.344,48.708,84.072,119.436,154.8,190.164},
    x_count = List.Count(x),
    y_count = List.Count(y),
    null_function = ( x_input as number ) => null, // returned in error cases
    Intervals = 
        List.Transform(
            {0..x_count - 2},
            each 
                let 
                    x_min = x{_},
                    x_max = x{_+1},
                    y_min = y{_},
                    y_max = y{_+1},
                    slope = (y_max - y_min)/(x_max - x_min)
                in
                    [
                        x_min = x_min,
                        x_max = x_max,
                        y_min = y_min,
                        slope = slope
                    ]
        ),
    InterpolationFunction =
        ( x_input as number ) =>
            let
                Interval =
                    // Handle special cases where x is below or above the entire range
                    if x_input < List.First(x)
                        then List.First(Intervals)
                    else if x_input > List.Last(x)
                        then List.Last(Intervals)
                    else
                    List.First (
                        List.Select (
                            Intervals,
                            each (x_input >= _[x_min]) and (x_input < _[x_max])
                        )
                    ),
                y_output =
                    Interval[y_min] + (x_input - Interval[x_min] ) * Interval[slope]
            in
                y_output,
    // If x_count <> y_count then return function that always returns null
    // otherwise return the proper function
    InterpolationFunctionFinal =
        if x_count <> y_count
        then
            null_function
        else
            InterpolationFunction
    in
        InterpolationFunctionFinal

There is some more error-handling that I ignored but hopefully this more-or-less does the trick.

Hopefully you can implement something in your model! Let me know how you get on 🙂

Regards,

Owen