Forum Discussion

emilmaican's avatar
emilmaican
Frequent Visitor
4 years ago
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. ...
  • Vijay_A_Verma's avatar
    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"