Forum Discussion
Solved
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. ...
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"
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.