Showing results for 
Search instead for 
Did you mean: 
Helper II
Helper II

Linear Interpolation From Matrix

The answer I am trying to find out is very similar to what @Greg_Deckler presented on "Quick Measures Gallery" about "Linear Interpolation". Here the links:



However, this time I need to interpolate within a matrix. Let's try to present the two tables we need to work with.



Table where we would like to get the result column called "RESULT". At the same time, it is the table from which, and based on some of its columns, we need to get into the second one (Lookup_Table) to perform the interpolation.

In this case, the two columns from which values need to be read in order to get into the second table are:

  1. Dir
  2. Prop_A










Table from which, and based on columns values from first table, interpolation needs to be performed.

Look that a Prop_A column is present in the second table and Dir values (from the first table) are grouped by 10 degrees from columns B-E.










Now, let's try to take an example.

Taking row number 5 from first table:

  • Vel = 141
  • Dir = 333
  • Prop_A = 0.398.


Therefore, we should get into the second table through next rows(Prop_A)/column(Dir):








Performing the linear interpolation of those values, Prop_A_Interpolated = 1.0872. Hence, the cell value in RESULT column in Main_Table should be equals to:

RESTULT = 1.0872 * 141 = 153.295.



From @Greg_Deckler solution, I assume a new variable needs to be defined in order to count for Dir values but I get stuck as soon as I try to group dir value for each 10° group in the Lookup_Table.


Any comment will be welcome!




Super User II
Super User II

Which column should be referenced if the Dir is 330? 

/ J

Connect on LinkedIn

If the value of a Dir (in Main_Table) is 330, this value should be referenced to column E [330-340) in Lookup_Table.

Solution Sage
Solution Sage

What I'd try to do in Power Query is:


- Modify your dir column in your main table by going Number.RoundDown([Dir]/10)

- Either rename the column headers in your lookup table so that [0-10) is 0, [10-20) is 1 etc, or modify it after unpivoting it


Question then is how Prop_A works in your lookup table

Thanks for your answer @jthomson.


Prop_A in the Lookup table is just a referecen column. Maybe, I should have changed its name.


Basically, the value of Prop_A in the Main_Table needs to be compared with the ones in Lookup_Table to know what row we need to be in Lookup_Table.

I didn't really have time to do the complete datamodelling, but this file handles your example calculation and if you create additional columns for each of the division intervals you should get the correct result. 

Take a look and see if it's along the lines of what you're thinking. If were on the right track i can fix it up and complete the remaining columns next week. 
Br ,

Connect on LinkedIn

Firstly, thank @tex628  very much for your answer.

I haven't had time to review it in depth yet but, difinitely, I will.


However, I have seen you used both M and DAX in your approach. I will take a look and come back again.


Thank you once again 🙂



Just @me whenever you're ready! 🙂 


Connect on LinkedIn

Helpful resources

PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors