Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
vito123
Helper II
Helper II

Need Help With creating an interpolation function and then calculating stresses

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 .

 

vito123_0-1692894418398.png

Stress Transfer function : with Value column up to 11

vito123_1-1692894502077.png

Bending Moment: With values up to 11

vito123_2-1692894622663.png

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 MomentY Stress value

x1

y1
x2y2
x3y3
x4y4
x5y5
x6y6
x7y7
x8y8
x9y9
x10y10
x11y11

 

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 IDMax Desired Bending MomentMin 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 IDStress function Stress Corresponding to the max desired bending moementStress Corresponding to the min desired bending moement
123Should 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 ?

1 ACCEPTED SOLUTION

Hi again @vito123

 

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

6 REPLIES 6
vito123
Helper II
Helper II

@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....

You're welcome @vito123 !

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn
vito123
Helper II
Helper II

Anybody ?

Hi @vito123 

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 🙂


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

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)

vito123_0-1693126462945.png

 

 

  • 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:

vito123_1-1693126607503.png

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...

 

Hi again @vito123

 

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors