How can I best combine my simple 'replace' queries into one?

[KristofferAJ]

Hi,

I have a long list of instutution names which spelled differently, they way we are sorting these duplicates is that we have over time manually harmonized the names by replacing the 'Facility name'

For example:

School of Apples vs. School of Apple

This leaves me with many single queries in powerBI which is identical to below..

= Table.ReplaceValue(TABLE,"Name XX","Name YY",Replacer.ReplaceValue,{"COLUMN"})

Is there a way I can have one query which I can keep come back to and simple add a line to is, whenever I want to make a new update? - this would also allow me to use this valuable information in other reports instead of starting from scratch.

I have tried various of ways like below, but I keep get an error

= Table.ReplaceValue(TABLE,"Name XX","Name YY",Replacer.ReplaceValue,{"COLUMN"}) AND
Table.ReplaceValue(TABLE,"Name XX","Name YY",Replacer.ReplaceValue,{"COLUMN"}) AND
Table.ReplaceValue(TABLE,"Name XX","Name YY",Replacer.ReplaceValue,{"COLUMN"}) AND
Table.ReplaceValue(TABLE,"Name XX","Name YY",Replacer.ReplaceValue,{"COLUMN"}) AND

[Anonymous]

If you have the replacement values either in a two column table, or in one column or

list of text like "Apple schools", "Apple School", then you can do:

= Table.FromRows(List.ReplaceMatchingItems(Table.ToRows(TableOrPriorStepName), each _, List.Zip({ReplacementTable[Column1], ReplacementTable[Column2]}))

Basically making your rows into a list, and then Zipping your replacement values together by position so you get the {{"apple schools", "Apple School"}, {"old schools", "New School"}} format that is required for List.TransformMatchingItems.

Give that a shot, I might have a parenthesis or bracket missing or in the wrong place, but I gotta get these dishes done.

--Nate

[KristofferAJ]

For instance similar to this, which change the type of columns for three columns in a go...

= Table.TransformColumnTypes
(#"site_Sheet",{{"Column1", type text}, {"Column2", type text}, {"Column54", type text}})

[KNP]

@KristofferAJ - I'd probably do this with a mapping table and functions. It makes it easier to maintain.

Check out this post...

https://community.powerbi.com/t5/Power-Query/Function-to-Replace-values-in-one-column-based-on-entri...

Specifically, the answer from @ImkeF, and the PBIX she attached.

[KristofferAJ]

Hi @Thingsclump 

Absolutely! - I have attached my example. In this case I have two duplicates I solve my replacing the Column name 'Facility Name':

That would be two steps in the query:

1.

= Table.ReplaceValue(BOOK1,"Inselspital Universitaetsspital Bern","Inselspital Universitaets Hospital Bern",Replacer.ReplaceValue,{"Facility Name"})

2.
= Table.ReplaceValue(BOOK1,"Orlando Clinical Research Center","Orlando Clinical Medical Center",Replacer.ReplaceValue,{"Facility Name"})

My big question can I combine these identical queries in one step? -and then each time I have a new simple update the same query with a new line?

Thanks

KRistoffer 

Account number	Region	Description	Scale	Section	ID	Facility Name	Facility Address	Facility City
343549875	west	description 1	5	A	DD15180	Inselspital Universitaetsspital Bern	Freiburgstrasse 18	Bern
323564	east	description 2	3	B	DD3420	Medizinische Universität Wien	Währinger Gürtel 18-20	Vienna
6757	east	description 3	6	C	DD29349	University of Miami Hospital	1400 Northwest 12th Avenue	Miami
769545	North	description 4	1	A	DD2446	Orlando Clinical Research Center	5055 South Orange Avenue	Orlando
3446	North	description 5	3	A	DD496479	Southwest General Healthcare Center	29 Barkley Circle	Fort Myers
343549875	west	description 1	5	A	DD15180	Inselspital Universitaetsspital Bern	Freiburgstrasse 18	Bern
769545	North	description 4	1	A	DD2446	Orlando Clinical Medical Center	5055 South Orange Avenue	Orlando
343549875	west	description 1	5	A	DD15180	Inselspital Universitaets Hospital Bern	Freiburgstrasse 18	Bern

[Thingsclump · Accepted solution]

Hi @KristofferAJ 

Below is a solution. Please feel free to accept as a solution if this works for you.

Have a table with old and new names.

In data table where you have all columns, create left join using old name as common column. add a conditional column that results in new column or old one if it is null. Basically if it is null, it means hospital name has not changed.

Code for first table.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8swrTs0pLsgsScxRCM3LLEstKgayU0uKoWJOqUV5Sjq4lSl45COrjNWJVvIvyknMS8lXcM7JzMtMBsoEpRanJhYlZyg4p+aVpBYBjcNQ4puaAqahKmJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Old name" = _t, #"New name" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Old name", type text}, {"New name", type text}})
in
#"Changed Type"

Code for second tabel:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("xZLPTgIxEMZfZcIZE3a33WWP/AngAUk0yoFwqMvINq5dMi0YfB4ewxsv5rQQSJSDJiQemk5npv31+9rZrJGIRIq8nclGs/GO1vG0QFuQXjldG4h47UsdHv1+JKN2i6NbY7GyK+1UBY9Gb5Asx+jsMddFMtw2INTPa1paR8pahKjNyVCbN5kcJzIVnEH1AxvzOvHNAZuI2FPHuNAf2mhblHjG7ncOpho9b7rflaTNEgmG+09yWDHyJux94g6jAjfNZHaZ6okpj16gxnkico5OpC3ULzDW6k3DqD4o5XIkWi24q8mV3j6IYldCZ4Nmjf7KvjtQszSXwjsZWr+BvQvRyeVYCH+LCVXKLGroVay5YFfv0aKiooQeGofkX6YlJTzUa0ZOSLHwM/m4++D04cBLZHk0+kAWeSoyrzmcGfQM0SAxfISqcmWhCM/4OIeuotcKt9DTVFSeO2AIjLds2BH9X9/rCob7D+fnv/t9TdGnz/YL3fMv", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Account number" = _t, Region = _t, Description = _t, Scale = _t, Section = _t, ID = _t, #"Facility Name" = _t, #"Facility Address" = _t, #"Facility City" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Account number", Int64.Type}, {"Region", type text}, {"Description", type text}, {"Scale", Int64.Type}, {"Section", type text}, {"ID", type text}, {"Facility Name", type text}, {"Facility Address", type text}, {"Facility City", type text}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Facility Name"}, #"Old vs New names", {"Old name"}, "Old vs New names", JoinKind.LeftOuter),
#"Expanded Old vs New names" = Table.ExpandTableColumn(#"Merged Queries", "Old vs New names", {"New name"}, {"Old vs New names.New name"}),
#"Reordered Columns" = Table.ReorderColumns(#"Expanded Old vs New names",{"Account number", "Region", "Description", "Scale", "Section", "ID", "Facility Address", "Facility City", "Facility Name", "Old vs New names.New name"}),
#"Added Conditional Column" = Table.AddColumn(#"Reordered Columns", "New name", each if [Old vs New names.New name] = [Facility Name] then [Old vs New names.New name] else [Facility Name])
in
#"Added Conditional Column"

Thanks,

Thingsclump

[Thingsclump]

Hi @KristofferAJ 

Can you share sample data in table format removing senstive information. This will help to give precise solution.

Thanks

www.thingsclump.com