Modern self-service BI products like Power Pivot in Excel and Power BI Desktop are often used to mash up data from different sources. Once multiple tables are imported into the model, users create relationships among them so that they can easily slice and dice data using any column from related tables. For those users of Power BI Desktop who only create relationships in the Manage Relationships dialog box, Figure 1, or connect columns in the Diagram View, Figure 2, they often run into constraints which block them from creating the relationships they need. For example, users cannot create a relationship by joining more than one column from each table. In this blog post, I am going to show you a way to work around such constraints inside Power BI Desktop.
In Power BI Desktop, users can only create a relationship between a pair of tables by joining two columns, one from each table. Furthermore, at least one of the columns cannot contain any duplicate values or blank values; otherwise, you get errors seen in Figure 3 or Figure 4.
Users who are familiar with relational database technologies can recognize that relationships are modeled after foreign key to primary key constraints. In relational databases a primary key column uniquely identifies each record in the table, must contain unique values, and cannot contain NULL values.
If you have already imported tables into the model but cannot create relationships due to the above requirements, you are typically told to work around the constraints by
- creating a calculated column to combine multiple join columns into a single one so you can create relationships between two calculated columns;
- introducing a third table that contains unique and non-blank values of the join columns and then creating two relationships between each of the original tables and the third table so that the original tables are related indirectly.
The introduction of bi-directional cross-filtering and calculated tables in Power BI Desktop has made both techniques easier to implement than in Power Pivot. Today I want to show you another way of working around the constraints: sometimes you can join two tables inside Query Editor and then bring a single table with all the columns you need into the model.
In our first example, we want to create a relationship between the two [ProjectID] columns as shown in Figure 5. [ProjectID] is unique in Table1 except sometimes it is blank when a program has been created without any project yet.
Assume we want to perform some cost analysis on each program. Although we cannot create a relationship between Table1 and Table2 due to the blank value, it’s sufficient if we can bring [ProgramID] into Table2. Here is how to achieve this in Power BI Desktop.
Once you have selected Table1 and Table2 in the Navigator, click the Edit button, as shown in Figure 6.
Select Table1 and then click the Properties button in the ribbon as shown in Figure 7.
Uncheck “Enable load to report” and then click OK, see Figure 8. We bring in Table1 just so we can merge its [ProgramID] column into Table2, afterwards, it has served its purpose therefore no longer needed when we build reports.
Select Table2 in Query Editor and then click the Merge Queries in the ribbon, see Figure 9.
In the Merge dialog box, click [ProjectID] under Table2, select Table1 in the dropdown box, click [ProjectID] under Table1, select “Full Outer (all rows from both)” as Join Kind, and then click OK. We have chosen “Full Outer” just in case we want to build reports including programs without any projects yet.
A new column has been added to Table2 where each row shows Table. The previous step only makes columns of Table1 available in Table2, but we still have to actually pick the individual columns we care about. Click the button at the upper right corner as shown in Figure 11.
Since we just want to bring [ProgramID] into Table2 with the same name, keep only ProgramID checked, uncheck the other two boxes and then click OK, see Figure 12.
Table2 now has the [ProgramID] column, we can close Query Editor, shown in Figure 13, and start building reports.
As a second example, we examine Table3 and Table4 as shown in Figure 14. We want do some analysis on Table3 but we also need the [Principal Amount] column from Table4.
We cannot create a relationship directly since we must join by both the [Entity Code] column and the [Fiscal Month] column. Although we can create a calculated column on each table to combine [Entity Code] and [Fiscal Month], it’s an artificial and strange thing to do. Instead we will leverage Query Editor’s flexible ability to merge two tables. The steps are identical to those in the first example except that we pick two columns from each table in the Merge dialog box. You can select multiple columns from a single table by holding the Ctrl button and then clicking the columns you want. Note that the order of clicking is important as it determines which pairs of columns will be matched during the merge. The UI conveniently labels the columns with numbers in the order you click, as shown in Figure 15.
Today I described a way to work around limitations of relationships by merging tables in the Query Editor before importing them into a model. Strictly speaking, this technique is not the same as creating relationships but, if used correctly, often gives us what we need. Users who are familiar with Power Query Formulas know that they can join tables in more flexible and powerful ways than allowed by the UI. By packaging two powerful engines, one from Power Query and one from Power Pivot, into a single product, Power BI Desktop gives users maximum versatility to shape their data any way they want.