Power Query finds and displays merge, but returns null when expanding the table

Hi guys,

Based on an ID number, I would like to merge two tables (both stored in the Data Model). First look gives a hopeful guess, matching almost all rows.

PowerQueryMatching.png

Also, when I select the "table" cell of the second table:

select.png

Under the table, it tells me I get a match indeed!

MatchIntable.png

BUT! Upon expanding the table, I receive "null". Both "Accession numbers" are of the same type, and there are no unprintable characters, case differences, or spaces.

null.png

I've got Excel's 2016 version with the following ribbon:

null.png

Could someone help me with this annoyance?

With kind regards,

Max

scroll down merged table to see if there is your data from HDB
Hard to say something from the picture

No, my data is not there. Here two more explanatory pictures. In the first you can clearly see that there is a match.

Betterview.png

But here, it returns "null"

betterview 2.png

you've duplicates there (row 4,5)
everything is ok because appropriate IDs are in correct places, see row 4,5,8
what you want to achieve?
do you know how merging table with LeftOuter works?

What I would like to achieve is that every "protein accession number without decoy" gets extra information from the '"HDBTable".
I already did perform the LeftOuter, bu apparently I need another? Kind of confusing, since the duplicates underneath "Q8WU90" both get the information assigned.

We'll get there eventually

IMHO ID shouldn't be duplicated if it is a primary key from Access. ID identifies each unique record (should) but in this case it doesn't. But maybe I'm wrong
As you can read: LeftOuter (all from first matching from second)
I think you can:
remove duplicates (rows) from first query
remove dup[licates (rows) from second query
sort by ID A-Z both tables
then try to merge by Left Outer

this is theory
that way I can say maybe this, maybe that

The problem eventually wasn't there afterall; "close and load" creates a table, and correctly connects the data.
So here is my hypothesis: the power query window loads only parts of both tables, around 200 with a max of 1000 rows. I think that the preview window makes a match between two datasets with these first rows only.
I would not call it a bug, but a message like "to be calculated" or something like that would be more descriptive that "null".

Thanks Sandy for your help!

Do you happen to know whether there is an option in a "normal" table — just like with pivot tables — to group duplicate names (with different specifics) with a + sign/collapsable button, i.e. creating a pivot table with extra explanatory rows?

You are welcome
Thanks for the feedback

Originally Posted by max138

I would not call it a bug, but a message like "to be calculated" or something like that would be more descriptive that "null".

Call Microsoft

"Normal" table you mean Excel Table?
You can try with Filter/Custom Filter but I think this is not what you want
or Advanced Filter

Nah, a filter doesn't cut it. I'm looking for this:
example.png

In a pivot table that goes like walking, but I still have not stumbled upon a way to add extra descriptive data to pivot table's columns.

Btw, might my English be unclear, please say so. It's not my native language