Forum Discussion
Helper IIIHow can I best combine my simple 'replace' queries into one?
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
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
6 Replies
- Thingsclump
Resolver V
Hi KristofferAJ
Can you share sample data in table format removing senstive information. This will help to give precise solution.
Thanks
KristofferAJHi 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?
ThanksKRistoffer
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
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
- KNP
Super User
KristofferAJ - I'd probably do this with a mapping table and functions. It makes it easier to maintain.
Check out this post...
Specifically, the answer from ImkeF, and the PBIX she attached.
- 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}}) 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
