Data Wranglig with the Query Editor

[AlejandroPCar]

Hi! 

I need help with "raw" data. I have a table with a list thousands companies names. But, the problem is the name column has several different names for the same company trough the years. For example the company name in 2009 is "Muebles LTDA" , but in the year 2015 the name is "Muebles LTDA." and for 2014 is "MueblesLTDA" and 2016 has "muebles ltda". 

So even when the name it is the SAME for all the years, Power BI understand it as a different name by year because some years has uppercase, some lower, some has a dot at the end some has double space between words, etc... 

Is there a way to unify those names? I mean, have the original name column with the raw data and an other column with the data ready to go, so in front of every wrong name is the Real name. Something like this: 

Orignal Name	Real Name
muebles ltda	Muebles LTDA
Muebles LTDA.	Muebles LTDA
Mueble LTDA	Muebles LTDA
Muebles    LTDA	Muebles LTDA
Muebles LTDA	Muebles LTDA

Thanks for your help, have a nice day. 

Alejandro 

[v-xjiin-msft · Accepted solution]

@AlejandroPCar

In your scenario, you have thousands company names. And maybe one same company will have several different names. So you want to format these different names into a same one. Right?

First, you should know that in Power Bi, if you want to change the Upper/Lower cases. You just need to go to Query Edit -> Transform -> Format.

Then to achieve your requirement, I’m afraid first you have to make a mapping list manually then do the format. To do this, I would suggest you to use Power Query. Please refer:

Go to Query Edit in Power BI desktop - > Right Click your source table and go to Advanced Edit - > define a mapping function like below:

let 
    fnChoose_CustCode = (input) => let
    values = {
    {"MUEB", "Muebles LTDA"},
    {"AAAA", "AAAA"},
    {input,"Undefined"}
    },
    Result = List.First(List.Select(values, each _{0}=input)){1}
in
Result,

Source = …

Then create a new Custom Column, and specify it with expression like: = fnChoose_CustCode(Text.Range([Orignal Name],0,4)).

Reference: https://www.excelguru.ca/blog/2014/09/03/multi-condition-logic-in-power-query/

Thanks,
Xi Jin.

[AlejandroPCar]

Thank you @v-xjiin-msft