Merge one table to another based and return range values

[emilmaican]

Hi,

Please help me with this situation.

I have 2 tables: Data_Table & Dim_Table

I need to merge them (or create a  3rd table) and obtain the result in the picture in table Result_Table.

Basically, I need to merge to Data_table information from Dim Table

What would be the most efficient solution in terms of speed of query execution

Please note that I don't want to do this in DAX

Thanks

@Powerquery

[Vijay_A_Verma · Accepted solution]

Solution file uploaded to https://1drv.ms/x/s!Akd5y6ruJhvhuToRmZB1nb7-VcxB?e=n9nXkT 

Use below M code in Data_Table

let
    Source = Excel.CurrentWorkbook(){[Name="Data_Table"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "PointsFROM", each List.Last(Table.SelectRows(Dim_Table, (x)=> x[Month]=[Month] and (x[Points]<=[Points]))[Points])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "PointsTO", each List.First(Table.SelectRows(Dim_Table, (x)=> x[Month]=[Month] and (x[Points]>=[Points]))[Points])),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "ValueFROM", each List.Last(Table.SelectRows(Dim_Table, (x)=> x[Month]=[Month] and (x[Points]<=[Points]))[Value])),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "ValueTO", each List.First(Table.SelectRows(Dim_Table, (x)=> x[Month]=[Month] and (x[Points]>=[Points]))[Value]))
in
    #"Added Custom3"

[emilmaican]

Thanks a lot. I will use it and come back with feedback.

[johnt75]

Can you explain what summarization you are hoping to use? You don't appear to be simply taking the max & min values for the ranges

[emilmaican]

I'm trying to determine what is the "Value" for the total "Points" on each row in table "Data_Table". After having the requested information (ranges of POINTS and VALUES) I will apply a formula to estimate the most probable "Value" for my Points.

Ex:  I have 70 Points but I don't know the "Value".  Instead I know that for 50 Points the "Value" is 25.6  and for 100 Points the "Value" is 37.6.  

Now, I can estimate de "Value" for 70 Points based on various methods depending on each case (linear, logarithmic etc)