Search code examples
powerbi

PowerBI filtering rows from table visual when values are missing

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.

Solution

  • 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:

    enter image description here

    And this is what you get:

    enter image description here

    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"