Power Query Editor - merge queries gets zero matches
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Power Query Editor - merge queries gets zero matches
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Oct 2010
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Power Query Editor - merge queries gets zero matches

    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?

  2. #2
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,553
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: Power Query Editor - merge queries gets zero matches

    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 by sandy666; Jun 27th, 2019 at 05:36 PM.
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  3. #3
    New Member
    Join Date
    Oct 2010
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Power Query Editor - merge queries gets zero matches

    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.

  4. #4
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,553
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Power Query Editor - merge queries gets zero matches

    so use the second sentence from the previous post
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  5. #5
    New Member
    Join Date
    Oct 2010
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Power Query Editor - merge queries gets zero matches

    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.
    Last edited by hinsch; Jun 27th, 2019 at 06:07 PM.

  6. #6
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,553
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: Power Query Editor - merge queries gets zero matches

    Quote Originally Posted by hinsch View Post
    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 by sandy666; Jun 27th, 2019 at 06:20 PM.
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  7. #7
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,553
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: Power Query Editor - merge queries gets zero matches

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

    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  8. #8
    New Member
    Join Date
    Oct 2010
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Power Query Editor - merge queries gets zero matches

    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 by hinsch; Jun 27th, 2019 at 06:46 PM.

  9. #9
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,553
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: Power Query Editor - merge queries gets zero matches

    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 by sandy666; Jun 27th, 2019 at 07:05 PM.
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  10. #10
    New Member
    Join Date
    Oct 2010
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Power Query Editor - merge queries gets zero matches

    Quote Originally Posted by sandy666 View Post
    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.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •