Forum Discussion
Merge one table to another based and return range values
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
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"
4 Replies
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"Thanks a lot. I will use it and come back with feedback.
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)
