cancel
Showing results for
Did you mean:  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 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

7 REPLIES 7  Super User II

Which column should be referenced if the Dir is 330?

/ J  Helper II

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

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

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.  Super User II

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

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 🙂  Super User II

Just @me whenever you're ready! 🙂

Br,
J Announcements #### Welcome to the User Group Public Preview #### 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. #### 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
Users online (12,641)