March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Register Now- Power BI forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- Mobile Apps
- Developer
- DAX Commands and Tips
- Custom Visuals Development Discussion
- Health and Life Sciences
- Power BI Spanish forums
- Translated Spanish Desktop
- Training and Consulting
- Instructor Led Training
- Dashboard in a Day for Women, by Women
- Galleries
- Community Connections & How-To Videos
- COVID-19 Data Stories Gallery
- Themes Gallery
- Data Stories Gallery
- R Script Showcase
- Webinars and Video Gallery
- Quick Measures Gallery
- 2021 MSBizAppsSummit Gallery
- 2020 MSBizAppsSummit Gallery
- 2019 MSBizAppsSummit Gallery
- Events
- Ideas
- Custom Visuals Ideas
- Issues
- Issues
- Events
- Upcoming Events
- Community Blog
- Power BI Community Blog
- Power BI 中文博客
- Community Support
- Community Accounts & Registration
- Using the Community
- Community Feedback

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

- Power BI forums
- Forums
- Get Help with Power BI
- DAX Commands and Tips
- Need Help With creating an interpolation function ...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Need Help With creating an interpolation function and then calculating stresses

08-24-2023
09:42 AM

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 ?

Solved! Go to Solution.

1 ACCEPTED SOLUTION

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

08-30-2023
06:30 AM

Hi again @vito123

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

- I started with tables similar to what you had posted:
**Markov,****Stress_Transfer_Function, and****Bending_Moment.** - 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- 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. - 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. - 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.

- Converts the list of 11 x/y pairs to a list of 10 records, each containing
- Then I created an intermediate query called
**Interpolation_Functions**.- Starts with
**Stress_Transfer_Function** - Joins
**Bending_Moment**based on TowerModelId. - Expands the Value columns from
**Bending_Moment,**changing the names to**Bending_Moment.Value1**etc. - Merges the
**Value**column values into a list column called**y_values**. - Merges the
**Bending_Moment.Value**column values into a list column called**x_values**. - Adds a column which applies
**fn_Generate_Interpolation_Function**to**x_values**&**y_values**.

- Starts with
- Then I joined
**Interpolation_Functions**into**Markov**and expanded the**InterpolationFunction**column. - 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

6 REPLIES 6

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

09-12-2023
11:15 PM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

09-17-2023
12:36 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

08-26-2023
10:44 PM

Anybody ?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

08-27-2023
01:24 AM

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 🙂

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

08-27-2023
01:58 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

08-30-2023
06:30 AM

Hi again @vito123

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

- I started with tables similar to what you had posted:
**Markov,****Stress_Transfer_Function, and****Bending_Moment.** - 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- 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. - 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. - 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.

- Converts the list of 11 x/y pairs to a list of 10 records, each containing
- Then I created an intermediate query called
**Interpolation_Functions**.- Starts with
**Stress_Transfer_Function** - Joins
**Bending_Moment**based on TowerModelId. - Expands the Value columns from
**Bending_Moment,**changing the names to**Bending_Moment.Value1**etc. - Merges the
**Value**column values into a list column called**y_values**. - Merges the
**Bending_Moment.Value**column values into a list column called**x_values**. - Adds a column which applies
**fn_Generate_Interpolation_Function**to**x_values**&**y_values**.

- Starts with
- Then I joined
**Interpolation_Functions**into**Markov**and expanded the**InterpolationFunction**column. - 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

Top Solution Authors

User | Count |
---|---|

31 | |

19 | |

15 | |

14 | |

10 |

Top Kudoed Authors

User | Count |
---|---|

62 | |

25 | |

24 | |

22 | |

16 |