Fuzzy Matching / Merge – Power BI

A new video is recently added by EZPowerBI.com to explain Fuzzy Matching / Merge feature of Power BI. This is understood that this feature is presently in preview stage but it is fairly important as there are lot many use cases where you have to merge data where you can expect variations in connecting table. This can be resolved through fuzzy merge logic implemented in Power BI. You cannot say that the accuracy is perfect but it will save you a lot from extra efforts needed to clean your data in such scenarios. This video is highly recommended for those who want to get hold of this feature at this stage. Subscribe to my YouTube Channel as well as this site to keep watching up to date contents.

Append and Merge Tables in Power BI

There are scenarios where different departments are working separately and sending data to a central location for consolidation. Organizations where data governance is taken seriously, there are less efforts required in combining such data. However, in scenarios where there is considerable variance/difference between source files, it is difficult to append or merge multiple files.

This blog post is sharing basic features available with Power BI to tackle such scenarios. In this example we will use a sample excel file having necessary tables involved. Download Sample Excel File


Appending Tables

For Example related to append, following tables will be used.

List A

Code Item Price
1 Pencil 2
2 Eraser 3
3 Pen 5
4 Notebook 11

List B

Code Item Price
1 Pencil 2
2 Eraser 3
5 Marker 12
6 Whiteboard 45

Step 1

Open a Power BI desktop file and click on “Get Data” icon from the ribbon. Select “Excel” and paste following URL in filename section.

https://ezpowerbi.files.wordpress.com/2018/07/ezpowerbi-com-merge-and-append-sample-data-012018.xlsx

Get Data

Note: If you have downloaded this file, you can select file from your folder.

Step 2

Select checkboxes of “List A” and “List B” from the list and click “Edit”

Step 3

You will notice that Power BI Query Editor window will open after previous step.

Note: If for any reason this window is not opening directly, then you have to click on “Edit Query” icon on the ribbon.

Step 4

Right click on Query Pane and select as follows
New Query>Combine>Append Queries as New

EZPowerBI.com Append Queries - 1

Step 5

In order to append files, you need to select following files as mentioned below. Since in our example, we are using only two tables, multiple tables can be selected if second option “Three or more tables” is clicked. Press “OK” when correct files are selected.

Append files

Step 6

You will notice that now both tables are combined and showing collective list.

1 Pencil 2
2 Eraser 3
3 Pen 5
4 Notebook 11
1 Pencil 2
2 Eraser 3
5 Marker 12
6 Whiteboard 45

However this list has duplicate values. These were purposely entered in the list to show you how we can get rid of those.

Step 7

Click on “Remove Rows” icon from the ribbon and select “Remove Duplicates”. This action will give you final list with unique items from both lists.

Remove Duplicates

This will complete your append operation.

Final list will be like below.

1 Pencil 2
2 Eraser 3
3 Pen 5
4 Notebook 11
5 Marker 12
6 Whiteboard 45

Step 8

You can rename new combined table as “Combined_Appended” for this example.

Step 9

Click on “Close and Apply” icon on the ribbon to complete this activity.


Merging Tables

Merging of tables is required when you are combining different tables with some common columns to make use of required columns of other tables. This is helpful in situations where product specification is maintained in one table while product prices are maintained in another. Following tables are used to this scenario.

1- Combined_Appended

2- List C

To import List C table, follow similar steps like mentioned in Step 1 of previous example (Append Tables).

Get Data

Step 2

Select checkbox of “List C” from the list and click “Edit”

Step 3

You will notice that Power BI Query Editor window will open after previous step.

Note: If for any reason this window is not opening directly, then you have to click on “Edit Query” icon on the ribbon.

Step 4

Right click on Query Pane and select as follows
New Query>Combine>Merge Queries as New

merge

Step 5

Now select files as demonstrated below. After selecting files click on “Code” column in both tables. This will make selected columns gray to identify columns used for merging tables.

Merge File Selection

Now click on “OK” to confirm merging.

Step 6

Once merged table is loaded, click on the icon on the top right corner as mentioned below to expand table columns.

expand

Step 7

You will see following window like below after completing previous step.

Select Columns

Now select Columns not available in first table to avoid duplicates. I our case we will select only “Box” and “Units” columns. In this example we can deselect “Use original column name as prefix” checkbox.

Select Columns2

Click on “OK” to complete this step.

Step 8

You will now see a merged table with combined columns like below.

merged file final

Step 9

Click on “Close and Apply” icon on the ribbon to complete this activity.