PowerBI filtering rows from table visual when values are missing

Question

I have two tables in a data model linked with a many-to-one relationship, cross filtered in both directions.

Table A has one row per value of the key, table B has many.

Not everybody from Table A appears in Table B.

I want to create a table visual which lists everybody in Table A, together with information from Table B, if present. However, when I add a variable from Table B to the visual, rather than displaying an empty cell for missing values, PowerBI automatically removes everybody from Table A who doesn't appear in Table B.

What I've got in the data model:

Table A

Name	Birthday
John	2-Apr-1962
Jane	3-Nov-1970

Table B

Name	Pet
John	Cat - Henry
John	Dog - Spot

The visual I want:

Name	Birthday	Pet
John	2-Apr-1962	Cat - Henry
John	2-Apr-1962	Dog - Spot
Jane	3-Nov-1970

What I'm getting:

Name	Birthday	Pet
John	2-Apr-1962	Cat - Henry
John	2-Apr-1962	Dog - Spot

There are no filters on my visual.

If I filter the visual to blank / missing values of the variable from Table B, I get an empty table.

I've looked at a few threads here on combining tables with missing data, but they're all about creating new tables in a data model rather than a visual.

Answer 1

By default PowerPivot does INNER joins. But if you need OUTER joins there is an easy solution (However, don't use bidirectional filtering, if it's not needed): On the table field list go to Pet, mouse right-click, select Show items with no data:

And this is what you get:

It's also straight forward to LeftOuter Join the 2 tables in PowerQuery:

let
    Source = Table.NestedJoin(
        #"Table A", {"Name"}, 
        #"Table B", {"Name"}, 
        "Table B", JoinKind.LeftOuter),
    #"Expanded Table B" = Table.ExpandTableColumn(
        Source, "Table B", {"Pet"}, {"Pet"})
in
    #"Expanded Table B"