cancel
Showing results for
Did you mean:

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

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 .

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.

1 ACCEPTED SOLUTION
Super User

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
Blog
6 REPLIES 6
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....

Super User

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

Kind regards,

Owen

Owen Auger
Blog
Helper II

Anybody ?

Super User

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
Blog
Helper II

Thanks for looking in to it @OwenAuger

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

Super User

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
Blog

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors