Forum Discussion

AlejandroPCar's avatar
AlejandroPCar
Icon for Helper IV rankHelper IV
8 years ago
Solved

Data Wranglig with the Query Editor

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 NameReal Name
muebles ltdaMuebles LTDA
Muebles LTDA.Muebles LTDA
Mueble LTDAMuebles LTDA
Muebles    LTDAMuebles LTDA
Muebles LTDAMuebles LTDA

 

 

Thanks for your help, have a nice day. 

 

Alejandro 

Need Help
  • v-xjiin-msft's avatar
    v-xjiin-msft
    8 years ago

    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.

2 Replies

  • v-xjiin-msft's avatar
    v-xjiin-msft
    Icon for Solution Sage rankSolution Sage
    8 years ago

    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.