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

[Silverborn]

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.

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.

• 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!

 

[RoryA]

It looks like your article numbers have imported as text, so try:

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

 

[Silverborn]

=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]

If you have a mixture, then leave it as it is. Appending "" won't affect text, and will convert numbers to the text equivalent.