Power Query Editor - merge queries gets zero matches

hinsch

New Member
Joined
Oct 28, 2010
Messages
8
I have 2 sources: Data (Excel spreadsheet) and item_info (SQL table). They both share a field item_no. I want to join on this field in Data to bring in additional fields from the item_info table into Data.

Both fields are data type text.
There are no leading or trailing spaces.
Join kind is left outer (all from Data, matching from item_info)

I'm getting 0 matches.

If I open Data as a spreadsheet, and bring a query of item_info into a table in a spreadsheet, I can do a VLOOKUP on the field item_no and get results just fine.

Why won't the merge work in Power Query????

I don't have permission to attach a sample of the Data spreadsheet, maybe that would be helpful in identifying the issue?
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,364
check the size of the letters, PQ is case sensitive so Lookup value is not the same as lookup Value

edit:
you can post a link to shared excel file with the representative source data and expected result, use OneDrive, GoogleDrive , DropBox or any similar
 
Last edited:

hinsch

New Member
Joined
Oct 28, 2010
Messages
8
Additional details:

The item_no data in the Data spreadsheet originated from the item_info table. Where there area alpha characters, they are the same case in both files, I haven't found issues with case not matching. :(
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,364
I have 2 sources: Data (Excel spreadsheet) and item_info (SQL table).
where is the second data?

copy representative data from SQL into the excel file (as is)

but first check data type in both sources. In linked file Item is as text, so maybe in SQL is a number or mixed. try to set Item column to the same type of data
 
Last edited:

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,364
both columns from these sources should have the same data type:

 

hinsch

New Member
Joined
Oct 28, 2010
Messages
8
I added a shared file of the SQL saved.

If I'm doing it right in PQ, both fields have data type Text

 
Last edited:

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,364
hmm, it works to me...

hinsch.zip

to see how it works you will need to change path to the source files. Now this is: D:\test\hinsch\Data_sample.xlsx so you can change it in Data Source Settings

because I don't know which columns you want in the result I expanded all
 
Last edited:

hinsch

New Member
Joined
Oct 28, 2010
Messages
8
hmm, it works to me...

hinsch.zip

to see how it works you will need to change path to the source files. Now this is: D:\test\hinsch\Data_sample.xlsx so you can change it in Data Source Settings

because I don't know which columns you want in the result I expanded all
Thanks for your help. Since the join works on the exported data, I decided to bring the item_info into a worksheet and then add it to the Data Model from there. That works. Mystified, but I can at least move forward.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,256
Messages
5,467,611
Members
406,544
Latest member
Aditya_Shanmugham

This Week's Hot Topics

Top