Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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)"
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)"
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
You may want to check out the Fuzzy Join functions.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.