The April 2019 update to Power BI has many good features, as this blog explains
Part six of a nine-part series of blogs

The April 2019 update to Power BI introduces a new concept: the ability to make the value of a property dynamic. Other welcome new changes include M Intellisense, data profiling in query editor and cross-report drill-down.

  1. The April 2019 Update to Power BI Desktop
  2. Conditional titles for visuals and conditional URLs
  3. Intellisense in the M Query Editor Language
  4. Data profiling in Query Editor
  5. Cross-report drill-through
  6. Fuzzy merging (this blog)
  7. Power BI Dataflows
  8. Other changes in the April 2019 update
  9. Power BI features waiting in preview, as of April 2019

For a cumulative list of all of the updates to Power BI Desktop in the last few years see this blog, or have a look at the Power BI courses that we run.

Posted by Andy Brown on 18 April 2019

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Fuzzy merging

Another feature emerging from preview, fuzzy merging allows you to create joins between fields if they are more or less the same. 

Suppose you have loaded into Query Editor a list of US Presidents:

Recent presidents

The four most recent US presidents at the time of writing.

You also have scores that they has registered in some completely made-up test!

Scores

Unfortunately, every one of the presidents' names has been misspelt.

If you want to show the average score for each president, you could merge the two tables:

Merging queries

Choose to create a new query, merging the two tables together.

 

Initially, this would yield no matches:

Matched rows

Initially the two columns don't have any matches.

 

If you tick the box Use fuzzy matching to perform the merge above, things improve:

Changing similarity threshold

if you adjust the similarity threshold down to 0.4 (the default is 0.8), you can get 6 matches out of 6.

 

Here's what the query returns for the settings above:

Query results

This is after choosing to display the president name from the Presidents table.

You can also specify a transformation table (for example, to say that Bill and William should be treated as the same text for fuzzy merges).

This blog has 0 threads Add post