#N/B trying to use x.lookup from a PowerQuery Table

Silverborn

New Member
Joined
Sep 5, 2022
Messages
5
Office Version
  1. 365
  2. 2003 or older
Platform
  1. Windows
Hello, I'm a little new to Excel and completely new to this forum.
I've run into an issue I cannot wrap my head around since variations work just fine but I feel I'm missing knowledge nobody inhouse can teach me, but I'm trying to understand why my formula isn't working. I'm not native English, so if I'm a little vague, that might be it. Long story short:

For work I've had to create a prototype in which I import our raw files and translate them into a usable table. Basic Power Query. I managed to get that to work. 17.000+ rows imported perfectly.

1662382894096.png


Now in a different sheet we have tabs that will house specific parts of the main list for product types. Because our system is a little messy, I can't just sort them on article numbers because we will not include all of them. Our current fix is to simply seperate them by hand. So long story short: I'm trying to use my query table to lookup discriptions (omschrijving 1) into another sheet.

  • So I have 1 sheet = ImportTBE which houses the picture above for 17.000 positions, which will grow with time. Power Query seems to be the most logical choice.
  • I have another sheet which houses a list that are part of an article group. These are hand-picked article numbers that include all information for that group + semi-heavy calculations to calculate the margins and sale-prices.
  • The main goal of this 'program' is to ultimately create a excel file in which we can increase prices incredibly fast by exporting the same file into an export to throw it back into our main database. No more 1-by-1 article mutations for our rather large amount of positions.
1662383482396.png


  • I've tried using the following formula: =X.ZOEKEN([@[ArtikelNr.]];ImportTBE_1[ArtikelNr.];ImportTBE_1[Omschrijving 1]) this results in: #N/B
  • To test out something different I copied the raw data into a differet sheet (no Power Query) and turned it into a table.
    • If I use the formula =X.ZOEKEN([@[ArtikelNr.]];Tabel2[[ ARTIKEL]];Tabel2[BEZEICHNUNG1]), I do get the correct results you see in Column F.
Does anyone know what I'm doing wrong, and why I can't use the Power Query table to get my product names. My constant (ID) is the article numbers. For some reason it sees the Power Query table as a different table compared to a regular table.

Anyone know how to pull out data from my raw file based on (constant) article numbers? Is it the formula, or can I use Power Query to give me my names, numbers and data back?

Thanks in advance!
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
39,795
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
It looks like your article numbers have imported as text, so try:

Excel Formula:
=X.ZOEKEN([@[ArtikelNr.]]&"";ImportTBE_1[ArtikelNr.];ImportTBE_1[Omschrijving 1])
 
Solution

Silverborn

New Member
Joined
Sep 5, 2022
Messages
5
Office Version
  1. 365
  2. 2003 or older
Platform
  1. Windows
=X.ZOEKEN([@[ArtikelNr.]]&"";ImportTBE_1[ArtikelNr.];ImportTBE_1[Omschrijving 1])
Nice that actually worked! So here is the thing. Our article numbers aren't only numbers. Sometimes they include letters. So to turn your answer into a proper lesson. Is your formula the correct approach (and why did yours work?) or should I change the Power Query file?
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
39,795
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
If you have a mixture, then leave it as it is. Appending "" won't affect text, and will convert numbers to the text equivalent.
 

Forum statistics

Threads
1,181,633
Messages
5,931,098
Members
436,775
Latest member
Taproot007

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top