Linear Interpolation From Matrix

[Anonymous]

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:

• Linear Interpolation with Power Bi
• Linear Interpolation

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

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.

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!

Lookup_Table.xlsx

Main_Table.xlsx

[tex628]

Which column should be referenced if the Dir is 330? 

/ J

[Anonymous]

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]

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

[Anonymous]

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.

[tex628]

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 ,
J

[Anonymous]

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 🙂

[tex628]

Just @me whenever you're ready! 🙂 

Br, 
J