power Query similar to Xlookup in excel

[maiiiii]

HI, I am trying to use power query that perform similar funtionality as xlookup. 

I have 2 tables Table 1 and Table 2. I am trying to look for a matching data in Table 2 and if there are no match found, it returns the next larger number. In excel, i can use xlookup (table1, table2, table2,) with match mode of  1. But I am struggling in converting this to power query. 

1 - Exact match. If none found, return the next larger item.

Table 1

3

2

5

Table 2

1.1

2.5

3.3

6

Result:

3.3

2.5

6

[v-heq-msft · Accepted solution]

Hi @maiiiii ,
You can try the follow code
Create a function

let
    NextLarger = (lookupValue as number, lookupTable as table) as number =>
    let
        // Filter the table to find values greater than or equal to the lookup value
        FilteredTable = Table.SelectRows(lookupTable, each [Column1] >= lookupValue),
        // Get the first row from the filtered table
        FirstRow = Table.FirstN(FilteredTable, 1),
        // Extract the value from the first row
        Result = if Table.IsEmpty(FirstRow) then null else FirstRow{0}[Column1]
    in
        Result
in
    NextLarger

Invoke the function in table 1

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlKK1YlWMgaTpkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}}),
    SortedTable2 = Table.Sort(#"Table 2",{{"Column1", Order.Ascending}}),
    AddCustom = Table.AddColumn(#"Changed Type", "Result", each NextLarger([Column1], SortedTable2))
in
    AddCustom

Final output

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[dufoq3 · Accepted solution]

Hi @maiiiii, another solution:

Result

let
    Table1 = Table.FromList({3,2,5}, (x)=> {x}),
    Table2 = Table.FromList({1.1, 2.5, 3.3, 6}, (x)=> {x}),
    StepBackT1 = Table1,
    Ad_Xlookup = Table.AddColumn(StepBackT1, "Xlookup", each List.Select(List.Buffer(List.Sort(Table2[Column1])), (x)=> x >= [Column1]){0}?, type number)
in
    Ad_Xlookup

[v-heq-msft · Accepted solution]

Hi @maiiiii ,
You can try the follow code
Create a function

let
    NextLarger = (lookupValue as number, lookupTable as table) as number =>
    let
        // Filter the table to find values greater than or equal to the lookup value
        FilteredTable = Table.SelectRows(lookupTable, each [Column1] >= lookupValue),
        // Get the first row from the filtered table
        FirstRow = Table.FirstN(FilteredTable, 1),
        // Extract the value from the first row
        Result = if Table.IsEmpty(FirstRow) then null else FirstRow{0}[Column1]
    in
        Result
in
    NextLarger

Invoke the function in table 1

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlKK1YlWMgaTpkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}}),
    SortedTable2 = Table.Sort(#"Table 2",{{"Column1", Order.Ascending}}),
    AddCustom = Table.AddColumn(#"Changed Type", "Result", each NextLarger([Column1], SortedTable2))
in
    AddCustom

Final output

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[wdx223_Daniel]

NewStep=let a=Table.Buffer(Table.Sort(Table2,{"ColOfTable2"}) in Table.AddColumn(Table1,"ValueFromTable2",each Table.Skip(a,(x)=>x[ColOfTable2]<[ColOfTable1]){0}?[ColOfTable2]?)