Power BI is a great tool to use when we have structured data. We want our tables to be related and easily readable by the person creating the report. However, we don’t always necessarily have data that is well formatted. Here are several tips and tricks to create a well-structured semantic model in case you don’t have that well-maintained data model.
It is crucial to have tabular data to build visualizations in Power BI. Without it, our reports wouldn’t be functional.
To mark as a table:
- Highlight the table and click Format as Table. (This allows us to read a single table, rather than the whole sheet.)
- Go to the design tab and change the properties of the table. By default, it will be ‘Table 1’.
This can be now be read by Power Bi as a separate entity to its sheet.
Once imported into Power BI, you will notice that we have 6 different columns: one ‘Name’ Column and 5 different columns with a date as the name. This is not easy to work with since we would have to create a Calculated Column in Dax to aggregate the data for all days. In this case, it is best to unpivot the date columns. To do this:
- Click on the Edit Queries button in the Home tab, which will navigate us to Power Query.
- Click on the top of the first column, then Ctrl + Shift+ Left Click the last column, which should highlight all the date columns shown below.
- Click the Unpivot button in the Transform tab. Now we will see hours for multiple dates and names represented in the value column.
- Change the name of the Attribute Column to ‘Date’ and the Value Column to ‘Hours’. We now transformed to have an entry for each person and for each date.
Now let us add an ID to help us distinguish between each entry.
- Go the Add Column Tab and select the index column drop down. Select ‘From 1’.
- Drag the Column to the left side to reorder it. Once done it should look like this:
Close and Apply the changes. Now we will have 4 columns: ID (which can be hidden), Name, Date, and Hours. We can now create a simple bar chart with total hours per person:
Creating relationships allows us to filter values on a table from a different table. This is necessary if you want to compare related values from many tables in your visualizations.
When creating relationships, we have the option of controlling both the cardinality and the direction of the filter.
Cardinality is the ratio of a count of matching values (ex. ID’s) in one table to the other. We aim for a carnality of one-to-many, or many-to-one depending on the order, and only one direction from the table with the single count to the table with many.
In the example, I created another table called Budget with the same structure as the table we created above, however, with different values.
We can see that we the same value for ‘John’ multiple times in each table. If we drew a relationship at this point, it would be a many-to-many relationship. This is not recommended, especially if you want your model to scale out to more tables.
An easy solution is to create a new master table that represents a distinct value from each table. To do so:
- Create a New Table in the Modeling tab called name, which will only display the names of the people.
- We then write a simple Dax formula:
- Draw our relationships to represent one to many.
We now are ready to build a visualization using all 3 tables. We want to make sure that we use the ‘NAME’ Column in the new table as an axis or intermediary between the two tables values.
Below is the ‘Line and Clustered Column Chart’ that compares the actual hours in the first table to the budgeted Project hours.
Many of the techniques above can also be replicated within Azure Analysis Services or SSAS (Tabular). Both Power BI and SSAS use a very similar GUI and both support DAX. Generally, we would like to upgrade to SSAS when our data starts to grow too large for Power BI. SSAS allows us to share our data model easily through-out the entire enterprise while also giving us several options of front-end tools to work with.