Power BI

Creating Surrogate Key in a data table


In our last Power BI training in Sydney, we spent a lot of time addressing the common issues in data models. A lot similar questions were raised in our Power BI training in Melbourne too so we decided to dedicate a complete blog archive to this topic.

Let’s summarize the concepts covered so far:

Getting Started

To enhance learning, we will disable the auto-detection of relationships by Power BI. This is done to create an environment where you can dive deeply into model creation. Disabling relationship detection is done purely to enhance concept building and is not preferred in ideal conditions.

Here is a step-by-step guide on getting started with Data Model Common Issues and Solutions.

Importing Data

The Importing Data phase of a data modeling involves procuring the right tables that can answer the business question at hand.

To Gathered Data for Data Model contains:

  • Product Sheet: It consists of three columns addressing the product details, i.e., Product Code, Product, and Product Type. Each Product has a distinct Product Code and falls under only one Product Type.
  • Product Line Prod Type Sheet: Each Product Type is a subcategory of Product Line. This sheet records the subcategory to category mapping.
  • Product List Sheet: The product list contains the details of each Product and the list price associated per unit.
  • Sales Person Per Product Type Sheet.: This sheet contains details of the Sales Person associated with the Sales. There exist many to many relationships between the salesperson and the product.
  • Sales Sheet: Sales sheet contains transactional Data for the Sales.

Now you have five queries loaded in your Query Editor. At this stage, your data model should contain five unconnected tables as shown in the image below

Data Preparation

Data Preparation involves ensuring that the data is capable of delivering value.

Data Preparation involves:

  • Checking the data types and data categories.
  • Making sure that all the required columns are present in data set.

Power BI guesses the data type of the column based on top 1000 Rows.

Creating Date Table

Date is an important dimension table in any data model. Date Table can be generated in Power BI using either the CALENDER() function or the CALENDERAUTO() function.

Both of these functions generate a single date column table. However, CALENDER(<start date>,<end date>) requires the starting and ending dates as the arguments to the function. Whereas, CALENDERAUTO() automatically detects the first and last date in the dataset and generates a date table accordingly.

Here is a step-by-step guide to Creating a very basic Date table.

Setting Model Design

Now that we imported the required data tables and created the date table, it’s time to design a model using this data. But before we design a data model, we will see what does the absence of relationship between these tables looks like.

The image below was created using Product Type from the Product table on the rows and Quantity Sold from the Sales Table on the values field for a Matrix Visual.

It can be seen that the visual shows incorrect values because1485 products are sold for every product type and the total quantity of products sold is also 1485. This means that the filter is not able to propagate because no relationship exists between the tables of the data model.

However, after we set the Model Design the tables are connected via relationship as indicated by the lines emerging from one table and ending on the other.  

Now that we have linked all the other tables and we do not find a key column to link the Sales Person table. Therefore, no relationship has been created for Sales Person table. However, let’s have a look at the matrix visual again:

The quantity sold for each product type has been updated.

Here is a step-by-step guide on Setting the Model Design in a Data Model

Creating Surrogate Key

Now you will create a Surrogate Key that can link the isolated Sales Person Table to the existing Data Model.  

1. Click on Home Tab in the Ribbon Menu.

2. Click on Edit Queries.

Query-Editor opens.

3. Click on Sales Person Query in the Queries Pan.

4. Click on Add Column Tab in the Ribbon.

5. Click on Index Column.

6. Click on From 1.

7. Rename the newly created column as Person ID.

8. Click Save and Apply.

Person ID is the Surrogate Key for the Sales Person Table.


Data Modeling consists of pulling the right data into Power BI for smooth analysis. This may seem easy but practically a good data model is the result of good practice and trouble shooting. In this blog post, the concept of creating Surrogate key using the query editor in Power BI desktop has been discussed.

Learning Power BI from Scratch? Get a complete, in-depth introduction to Microsoft Power BI tool by attending our Power BI Basic Training Course.

More Checkouts for Power BI Training in Australia.

Back to list

Leave a Reply

Your email address will not be published. Required fields are marked *