Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
maiiiii
New Member

power Query similar to Xlookup in excel

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

2 ACCEPTED SOLUTIONS
v-heq-msft
Community Support
Community Support

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

vheqmsft_0-1723096082357.png

 

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

 

 

View solution in original post

dufoq3
Super User
Super User

Hi @maiiiii, another solution:

 

Result

dufoq3_0-1723102517551.png

 

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

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

View solution in original post

3 REPLIES 3
dufoq3
Super User
Super User

Hi @maiiiii, another solution:

 

Result

dufoq3_0-1723102517551.png

 

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

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

v-heq-msft
Community Support
Community Support

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

vheqmsft_0-1723096082357.png

 

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
Super User
Super User

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

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors
Top Kudoed Authors