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?
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
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:
Upvote 0
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. :(
 
Upvote 0
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:
Upvote 0
both columns from these sources should have the same data type:

datatype.jpg
 
Upvote 0
I added a shared file of the SQL saved.

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

image.png
 
Last edited by a moderator:
Upvote 0
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:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,483
Messages
6,113,919
Members
448,533
Latest member
thietbibeboiwasaco

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
Back
Top