|Data Model (No Connections)|
Now that we've identified what each of the tables are, we can start looking at how "clean" they are. "Clean" is a term that generally refers to how usable a data set is for a specific purpose. For data modeling, you generally want your transaction tables to be flat. This means that each column should be a unique metric or slicer. Imagine that we had a dataset that contained a column for "2011 Sales" and "2012 Sales". We would want to pivot those down to a year column taking the values 2011 and 2012, and a Sales column that takes the appropriate value from the old columns. In our case, the data set is clean enough for an introduction. One thing that does stick out is the Product Hierarchy. There are columns for Product Category, Product Sub-Category, and Product Name.
|Data Model (No Connections)|
|Do Not Summarize|
|Data Model (Clean Orders)|
Next, we need to connect the Returns table to the Orders table. In Power BI Desktop, this is a simple task.
|Data Model (Clean Returns)|
First, open the Relationships canvas.
|Data Model (Connected Returns)|
This created a "one-to-many" relationship from Returns to Orders. This is exactly what we wanted. Now, we can create charts using "Status" as a slicer against measures from the Orders table. Let's move on to the Users table.
This table is a bit more interesting. We have each of the managers tied to their respective regions. However, we also have the CEO Pat tied to every region. This means that Region is not a unique key in this table. We want to remove Pat's rows from the bottom of the table, and add a new column labeled CEO which would take the value "Pat" for all 4 rows. There are some great ways to handle this using the Query editor (also known as Power Query). However, we'll take the easy way this time and save the hard work for a later post.
Let's start by opening the Query Editor.
The query editor is where you can edit your Data Import queries in a number of ways to get exactly the format you want. Let's open the Query for the Users table.
|Select Users Query|
This shows us what the Users table currently looks like, as well as shows us every step that was taken to create it.
Since we didn't mess with the queries, all of the steps listed are the defaults. What we want to do is remove the bottom 4 rows. We can do this by selecting the "Remove Bottom Rows" option from the toolbar and typing in the number 4.
|Remove Bottom Rows|
This leaves us with only the legitimate managers in the table.
|Users Table (Legit)|
Now, we apply these changes and go back to our model.
|Close and Apply|
Next, we need to add the CEO column and give it the value "Pat" for all rows. We start by navigating to the Data canvas and select the "New Column" button.
Next, we type the function CEO = "Pat" into the function bar.
|Users Table (Clean)|
This will automatically rename the column to CEO and populate it with "Pat" for every row. Finally, we need to connect this table to the Orders table using the Region key.
|Data Model (Complete)|
Now, we can slice and dice our Orders data by any of the slicers we want, including the slicers in the Returns and Users tables. Hopefully, this opened your eyes to how easy it is to build simple data models in Power BI Desktop. Stay tuned for later posts where we get more in-depth on how to handle these problems using best practices, as well as deal with more difficult issues. Thanks for reading. We hope you found this informative.