Forum Discussion

vicentetic's avatar
vicentetic
Frequent Visitor
5 years ago
Solved

Reduce to a single name different nominations for the same company

My problem is that I have a column with the name of a lot of companies and the same company is named in different ways. For example, the company Tdk is named "Tdk", "TDK", "Tdk Corporation", "Tdk Corporation Co." when is the same company. How can I reduce all the nominations to a unique one?

 

Thank you in advance

Need Help
  • v-xuding-msft's avatar
    v-xuding-msft
    5 years ago

    Hi vicentetic ,

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCnHxVorVAdIp2TBawTm/qCC/KLEkMz8PmxiQrRQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}}),
    #"Merged Queries" = Table.FuzzyNestedJoin(#"Changed Type", {"Name"}, #"Map Table", {"From"}, "Map Table (2)", JoinKind.LeftOuter, [IgnoreCase=true, IgnoreSpace=true, Threshold=0, TransformationTable=#"Map Table"]),
    #"Expanded Map Table (2)" = Table.ExpandTableColumn(#"Merged Queries", "Map Table (2)", {"To"}, {"Map Table (2).To"})
in
    #"Expanded Map Table (2)"

     

3 Replies

  • Jimmy801's avatar
    Jimmy801
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Hello vicentetic 

     

    do you have a list/table with all possible nomination and how it should be renamed? If this is the case you could some function that searches for all the different names of the company and replace it.


    If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
    Kudoes are nice too

    Have fun

    Jimmy

  • v-xuding-msft's avatar
    v-xuding-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi vicentetic ,

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCnHxVorVAdIp2TBawTm/qCC/KLEkMz8PmxiQrRQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}}),
    #"Merged Queries" = Table.FuzzyNestedJoin(#"Changed Type", {"Name"}, #"Map Table", {"From"}, "Map Table (2)", JoinKind.LeftOuter, [IgnoreCase=true, IgnoreSpace=true, Threshold=0, TransformationTable=#"Map Table"]),
    #"Expanded Map Table (2)" = Table.ExpandTableColumn(#"Merged Queries", "Map Table (2)", {"To"}, {"Map Table (2).To"})
in
    #"Expanded Map Table (2)"