locked
Merge Failing - What's the difference between =RELATED in the data model and the 2 merge query buttons - DMAdd-In 64bit RRS feed

  • Question

  • Hey everyone,

    I'm trying to merge two tables as a lookup. I have 2 tables of Item ID's along with characteristics and sales data, without duplicates, and a relationship created between them. I've cleaned both columns, gone through all the join options, and can't pull any of the data in. I'm left with NULLs. 

    I am able to pull in the lookup information using the Data Model and =RELATED but I can't pull in the data using merge. I've tried both merge query options in the Combine tab and the table drop down in the top left corner of the data. I was wondering if there's something else I've missed, or if I should just punch everything in using the data model. Does anyone have a few links of the difference between the two?

    Also, Does anyone know if there is a list I can sign on to for the release of the 64 bit version of the Data Mining Add In for Excel 2016?

    Thanks

    Friday, February 3, 2017 8:42 PM

Answers

  • Hi TGoodheart,

    In the Query Editor, if you select the columns you're attempting to match on, what data type do they have? (it should be displayed in the Data Type dropdown on the Home tab of the Query Editor ribbon.) Are the data types the same, or different?

    Select a cell in each of the tables that contains a value you expect should match. A small preview pane should appear below the table, showing the exact value contained in the cell. Look for things like differences in casing, leading/trailing whitespace, etc. You can even copy the values from this pane and paste them into a text editor for closer examination.

    Let me know what you discover.

    Ehren

    • Marked as answer by TGoodheart Tuesday, February 7, 2017 3:26 PM
    Monday, February 6, 2017 6:12 PM

All replies

  • Only thing I can think of here is the different handling of case-sensitivity: Power Query (M) is case sensitive, while PowerPivot (DAX) isn't.

    Imke Feldmann TheBIccountant.com

    Saturday, February 4, 2017 8:58 PM
  • Hi TGoodheart,

    In the Query Editor, if you select the columns you're attempting to match on, what data type do they have? (it should be displayed in the Data Type dropdown on the Home tab of the Query Editor ribbon.) Are the data types the same, or different?

    Select a cell in each of the tables that contains a value you expect should match. A small preview pane should appear below the table, showing the exact value contained in the cell. Look for things like differences in casing, leading/trailing whitespace, etc. You can even copy the values from this pane and paste them into a text editor for closer examination.

    Let me know what you discover.

    Ehren

    • Marked as answer by TGoodheart Tuesday, February 7, 2017 3:26 PM
    Monday, February 6, 2017 6:12 PM
  • Thank you for the response, I found two mistakes I had made: One column had a data type of Any and I had copied the applied steps over when I went to remake the query  and was missing the trim step each time.

    Thanks again

    TG

    Tuesday, February 7, 2017 3:27 PM