Power BI

Using Merge Feature to create key columns in Power BI

Due to COVID-19 outbreak, our Power BI training in Sydney was temporarily suspended but we were still providing email support to our former attendees of Power BI training in Sydney, Melbourne, Brisbane, Perth, Adelaide and other cities across Australia. We found that a few data modeling problems were common and therefore it was essential to create an archive that can assist all users.

Here’s a link to the archive covering common issues and the solutions in Data Modeling:

https://powerbitraining.com.au/category/data-modeling-issues-and-solutions/

Let’s summarize the concepts covered so far:

1. Importing Data

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

For a step by step guide on this: https://powerbitraining.com.au/data-model-common-issues-and-solution-power-bi-training-in-sydney-power-bi-training-in-melbourne/

2. 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.

3. 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.

For a step by step guide on this: https://powerbitraining.com.au/data-model-common-issues-and-solution-3-power-bi-training-in-sydney-power-bi-training-in-melbourne/

4. Setting Model Design

The model design consists of creating relationship between the data tables procured for the task at hand.

Common Modeling issues in Power BI training in Sydney

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. For a step by step guide on this issue: https://powerbitraining.com.au/data-model-common-issues-and-solution-4-power-bi-training-in-sydney-power-bi-training-in-melbourne/

5. Creating Surrogate Key

Surrogate Key links the isolated Sales Person Table to the existing Data Model. Creating a surrogate key in a table where it does not exist and then linking the Sales Person Table to the Sales table using this key is also a part of data modeling.

6. Merging Queries

In this blog post, we will address the problem of missing column using merging queries.

The Person ID column is still missing in the Sales Table. In order to create a relationship between Sales Person and Sales table, Person ID must be present in Both Tables. Therefore, we will use Merge query to add Person ID column in Sales Table.

To Merge Queries:

1. Click on Home Tab in the Ribbon Menu.

2. Click on Edit Queries.

Query-Editor opens.

3.Click on Sales Query in the Queries Pan.

4. Click on Merge Queries in the Combine Section.

Merge option box opens.

5Click on Sales Table in the drop-down menu for second table.

6. Click on Sales Person First Name. Press Ctrl on Key Board. Click on Sales Person Last Name from the Sales Table.

7Click on Sales Person First Name. Press Ctrl on Key Board. Click on Sales Person Last Name from the Sales Person Table.

Common Modeling issues in Power BI training in Sydney

8. Click on Double Headed arrow at the end of sales table.

9. Click on Person ID Column. Click OK.

Common Modeling issues in Power BI training in Sydney

10. Remove the Sales Person First Name and Sales Person Last Name in the Sales Table.

11. Click Close and Apply.

12. In the desktop file, click on Model Tab in the Left Ribbon.

13. Click on Person ID in the Sales Person Table and drag to the Person ID in the Sales Table.

A one-to-many relationship is created as a single person sold multiple products in a fact table.

Common Modeling issues in Power BI training in Sydney

All the tables have been linked and the data model is created.

Conclusion:

A perfect data model consists of the right number of tables with the right relation between the tables. The steps involved in creating a typical data model are importing data, data preparation, creating date table, setting model design. One of the common issue that occurs while creating the data table is the missing surrogate key. In the last blog from the series, the issue of creating surrogate key was discussed while in this blog post, the issue of missing column has been addressed using the merging queries option.

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 *