Forum Discussion
Merge one table to another based and return range values
- 4 years ago
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"
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
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)