Power Query Editor - merge queries gets zero matches

[hinsch]

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?

 

[sandy666]

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

 

[hinsch]

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]

so use the second sentence from the previous post

 

[hinsch]

Here's a Dropbox link to a sample of the Data spreadsheet

https://www.dropbox.com/sh/xp8l6ra2ugykqqp/AADs68c6-SB0P-bpuMTZi2kua?dl=0


I should clarify that the field in this table is "Item", not item_no as I stated previously.

 

[sandy666]

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

 

[sandy666]

both columns from these sources should have the same data type:

 

[hinsch]

I added a shared file of the SQL saved.

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

 

[sandy666]

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

 

[hinsch]

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.