Need Help With creating an interpolation function and then calculating stresses

[Anonymous]

Hi Guys, 

Let me try to explain my problem, please bear with me here.. I have three tables with the following structure: ( TowerModel ID is the common identifier)

Markov: The desiredmoment column corresponding to the towermodel ID  is what we are interested in .

Stress Transfer function : with Value column up to 11

Bending Moment: With values up to 11

Step:-1 Now I need to create an interpolation function between the 11 values of the stress transfer function and 11 values of the bending moment :

X Bending Moment	Y Stress value
x1	y1
x2	y2
x3	y3
x4	y4
x5	y5
x6	y6
x7	y7
x8	y8
x9	y9
x10	y10
x11	y11

After an interpolation function is generated with EACH ROW of the stress transfer function.

STEP:-2 For every tower Model ID in Markov I need to calculate the Max and Min of the desired bending Moment 

so ideally I could/should have another table

tower Model ID	Max Desired Bending Moment	Min Deisred bending moment
123	12345	-5648

Step:-3 Now with the interpolation function calculated in step:-1 which is like  Stress = function (Moment) , I should calculate the stress correponding to the 2 moments listed in step-2 so final table should look like :

tower Model ID	Stress function	Stress Corresponding to the max desired bending moement	Stress Corresponding to the min desired bending moement
123	Should track this column as well..	12345	-5648

I was able to do this using python but am having troubl with personal gateways for scheduled refreshes.

COuld somebody please help me here ?

[Anonymous]

@OwenAuger , you are a Genius !!!..

Thanks the solution works !! But I must admit that a newbie that I am I am struggling to fully grasp the function written here and make sense out of it as I would most certainly need to update it..
could you please point me to some resources that I can review to understand such complex functions .. that will be much appreciated  and thanks once again for your time....

[OwenAuger]

You're welcome @Anonymous !

For Power Query functions, there are numerous articles out there.

I would recommend Ben Gribaudo's Power Query M Primer:

https://bengribaudo.com/blog/2017/11/28/4199/power-query-m-primer-part2-functions-defining

Here's another on Radacad:

https://radacad.com/writing-custom-functions-in-power-query-m

Kind regards,

Owen

[Anonymous]

Anybody ?

[OwenAuger]

Hi @Anonymous 

I think I've understood your requirements 🙂

Just to confirm:

For each Tower Model ID you want to construct a piecewise linear function which interpolates between adjacent (bending moment, stress value) pairs:

• (x1,y1) & (x2,y2),
• (x2,y2) & (x3,y3),
• ...
• (x10,y10) & (x11,y11)

This function is then to be applied to the Max Desired Bending Moment & Min Desired Bending Moment columns of the Markov table to produce the two columns containing corresponding Stress Values.

You want to do all of this in Power Query.

Questions:

• Will it always be true that x1 < x2 < ... < x11?
• Is there a single row per Tower Model ID in each of Bending Moment and Stress Transfer Function tables?

Will post back with a suggested method shortly 🙂

[Anonymous]

Thanks for looking in to it @OwenAuger 

Please find below my response to your question:

Questions:

• Will it always be true that x1 < x2 < ... < x11?

Yes but please x1 is negative but the inequality that you have written still holds.. It becomes positive at around x8 ( but ofcourse this can vary)

• Is there a single row per Tower Model ID in each of Bending Moment and Stress Transfer Function tables?

Now for the stress transfer function table , there are several rows for each tower Model ID as there could be several stress transfer fucntions like below:

But for the bending moment there is a single row of tower MOdel ID and corresponding moments.

So the interpolation would have to occur several times even for the same tower...

And the out put table will also have multiple rows of tower MOdel ID correponding to several stress transfer function and associated Max and MIn stress that we calcualte..

I hope this clarifies.. please let m eknow if you need more informaiton...

[OwenAuger · Accepted solution]

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