Find and Replace Words in PowerBi

[Anonymous]

I have 2 data sets, and I want to build a relationship between the 2 data sets by discipline.

The only issue is in one data set the engineering discipline is abbreviated and in the other data set the discipline is not abbreviated.

For example Electrical in one is Elec in the other.

What is the simplest way to go about cleaning this data in Power Query (I am still learning PowerBi), so that going forward the relationship can be built (I will be re-freshing the data each week too)

[mussaenda]

Hi @Anonymous ,

Replacing values is an option. buuuuuut will not actually solve the issue.

If you have 'elec' and 'electrical' on the same dataset and you replaced 'elec' with 'electrical', (by any chance)

the 'electrical' word will be 'electricaltrical' Also replacing values are case sensitive. If in future you will have 'ELEC',

it will not be replaced as 'electrical' because it is all caps. These cases we need to reconsider.

Using table bridge is also a good solution.

I am also suggesting to use a custom column that uses text.contains and text.upper then declare what you need to make them same with the other one or to make them both common.

like

if Text.Contains(Text.Upper(yourcolumnname), "ELEC")

then "Electrical"

else your condition.

You can also add more conditions in just one custom column instead of replacing values more than once if ever you have more words to replace.

and fuzzy matching merge also is a great solution buuuuut consider the mins it will take to do this.

Hope this helps!

[Daviejoe]

Go to the Power Query Editor, select one of the tables in your query

[FrankAT]

Hallo @Anonymous 

build a new table which holds two columns, one with the abbreviated and one with the non-abbreviated data.

Use this table as a bridge table between your two data tables (see figure).

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

[amitchandak]

@Anonymous , refer if these can help

https://www.poweredsolutions.co/2019/03/26/fuzzy-matching-in-power-bi-power-query/

or try

https://docs.microsoft.com/en-us/dax/search-function-dax

https://docs.microsoft.com/en-us/dax/containsstring-function-dax

[Anonymous]

@Anonymous 

Go to Edit Queries and Select puticular filed (Elect)  then right click you have a option replace values ,  Enter the replac value click ok   that purticular value will change