cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
McMur
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.

 

Main_Table:

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

image.png

 

 

 

 

 

 

 

Lookup_Table:

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.

image.png

 

 

 

 

 

 

 

 

Now, let's try to take an example.

Taking row number 5 from first table:

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

image.png

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

image.png

 

 

 

 

 

 

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.

image.png

 

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!

 

Lookup_Table.xlsx

Main_Table.xlsx

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

jthomson
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. 
image.png
Br ,
J


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! 🙂 

Br, 
J


Connect on LinkedIn

Helpful resources

Announcements
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

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors