How to substitute NULL with value in Power BI when joining one to many

Question

In my model I have table AssignedToUser that don't contain any NULL values. Table TotalCounts contains number of tasks for each User.

Those two table joined on UserGUID, and table TotalCounts contains NULL value for UserGUID.

When I drop everything in one table there is NULL value for AssignedToUser. How can I substitute value NULL for AssignedToUser for "POOL".

Under EditQuery I tried to Create additional column

if [AssignedToUser] = null then "POOL" else [AssignedToUser]

But that didnt help.

UPDATE: Thanks Alexis. I have created FullAssignedToUsers table, but when I try to make a relationship with TotalCounts on UserGUID - it doesnt like it.

Data in new a table looks like this:

UPDATE: File .ipbx can be accessed here:

https://www.dropbox.com/s/95frggpaq6tce7q/User%20Open%20Closed%20Tasks%20Experiment.pbix?dl=0

Answer 1

I believe the problem here is that your join has UserGUID values that are not in your AssignedToUsers table.

To correct this, one possibility is to replace your AssignedToUsers table with one that contains all the UserGUID values from the TotalCounts table.

FullAssignedToUsers =
    ADDCOLUMNS(VALUES(TotalCounts[UserGUID]),
        "AssignedToUser",
        LOOKUPVALUE(AssignedToUsers[AssignedToUser],
            AssignedToUsers[UserGUID], TotalCounts[UserGUID]))

The should get you the full outer join. You can then create the custom column like you described in the table and use that column in your visual.

You'll probably want to break the relationships with the original AssignedToUsers table and create relationships with the new one instead.

If you don't want to take that extra step, you can do an ISBLANK inside your new table formula.

FullAssignedToUsers =
    ADDCOLUMNS(VALUES(TotalCounts[UserGUID]),
        "AssignedToUser",
        IF(
            ISBLANK(
                LOOKUPVALUE(AssignedToUsers[AssignedToUser],
                    AssignedToUsers[UserGUID], TotalCounts[UserGUID])),
            "POOL",
            LOOKUPVALUE(AssignedToUsers[AssignedToUser],
                AssignedToUsers[UserGUID], TotalCounts[UserGUID])))

---

Note: This is equivalent to doing a right outer join merge on the AssignedToUsers table in the query editor and then replacing the nulls with "POOL". I'd actually recommend approaching it that way instead.

---

Another way to approach it is to pull the AssignedToUser column over to the TotalCounts table in a custom column and use that column in your visual instead.

AssignedToUsers =
    IF(ISBLANK(RELATED(AssignedToUsers[AssignedToUser])),
       "POOL",
       RELATED(AssignedToUsers[AssignedToUser]))

This is equivalent to doing a left outer join merge on the TotalCounts table in the query editor, expanding the AssignedToUser column, then replacing nulls with "POOL" in that column.