A common task when cleaning data is to perform multiple replacements at once. When dealing with “translation tables” you might have come across Chris Webb’s solution using List.Generate to deal with this. This works fine – but might deliver unexpected results if you don’t consider it’s implications properly. Say we add lines like shown below, then we might expect the result as highlighted in yellow:
Multiple replacements using 2 different techniques
So hey – what happened here?: Our catamaran turns out as a bearamaran and the chair turns to chwater.
How it works:
Because there could be more than one word per line that needs to be replaced, the function Text.Replace alone wouldn’t be able to do this job as it takes only one replacement element. This is where List.Generate jumps in: Taking the result of the previous replacement step as input and performs the next replacement – as often as there are elements in the replacement list.
And Text.Replace is a function that searches for substrings in the text. That way “catamaran” turns out as a “bearamaran”.
But what happened to the mat where the cat and the dog sat on: “chwater”?
Firstly “mat” has been replaced by “chair”. But due to the recursive nature of the List.Generate-function, this result is not safe. So when Text.Replace kicks in on: Replace “air” with “water”, searching again also for substrings, a part of “chair” with be replaced by water: “chwater“.
So while Chris’ article is an excellent explanation of how List.Generate works, most multiple text replacement requirements in the real world might be better solved with a different approach:
Use List.ReplaceMatchingItems instead
This is a function that is designed to work on lists and performing replacements for multiple items (that also need to be fed in as list):
But we want to replace within a text, not within a list!
No problem for Power Query as the master of transformations: We just temporarily transform our text into a list, perform the replacements and then transform our list back into text – as if nothing has happened 🙂
Text.Split(text, ” “) will split out text strings into a list of single words because we choose blank as the delimiter
Text.Combine(list, ” “) will then reassemble.
This technique is useful in many other operations as well. Sometimes the delimiters will change, but as long as you don’t get too mad with your transformations in between, it is often a easy and safe way to take a useful function from the List-category to perform on Text-fields.
But there’s another hurdle to take here: How to get our translation table into the desired format for this function? Have a look at the documentation:
List.ReplaceMatchingItems ( {1, 2, 3, 4, 5}, { {2, -2}, {3, -3} } ) equals { 1, -2, -3, 4, 5}
So we’re fine with lists: Identifying them by their curly brackets like in the result: { 1, -2, -3, 4, 5}
But what the x is this?: { {2, -2}, {3, -3} } a list containing lists as it’s elements.
List of Lists
Have you ever worked with a table with one column only? Maybe also have been confronted with a surprising effect that this had automatically turned itself into list-format? So if our table just had one column, it would almost be a list already. So let’s try to get there: Merge the columns of the translation table into one so that the contents of the rows (each cell effectively) will be returned as a list (as we want to maintain the separation that the columns gave, but not their format).
Table.AddColumn(Source, “Liste”, each ( { [Word To Replace], [Replace With] } ) )
This is an added column where in each row of the table, the specific rows contents of [Word To Replace] and [Replace With] will be passed into the list. So for every single row one list will be created.
If you’re interested in the techniques behind this or have guessed that instead one list with 2 full column would be returned: [Word To Replace] and [Replace With]: The unimposing little word “each” actually creates a sort of a row context. Actually the expression at the 3rd place in the “Table.AddColumn”-expression is expected to be a function and that’s what “each” stands for. It is basically syntax sugar for a full function expression like described here.
So: Goodbye columns “WordsToReplace” and “ReplaceWith”, but we respect your destiny and maintain your separating character by keeping your contents separated in the list-format instead.
Nice side-effect: It’s also way much faster.
And here is the full code:
let
Source = ReplacementsTable,
CreateListOfLists = Table.AddColumn(Source, "Liste", each ({[Word To Replace], [Replace With]})),
Text = Text,
TurnTextToList = Table.AddColumn(Text, "Custom", each Text.Split([Text], " ")),
Replacements = Table.AddColumn(TurnTextToList, "Changed Text Expected",
each Text.Combine(List.ReplaceMatchingItems([Custom],CreateListOfLists[Liste])," ")),
Cleanup = Table.RemoveColumns(Replacements,{"Custom", "Text"})
in
Cleanup
& the file: M_MultipleReplacementsV3.xlsx
.. and a “functionized version”:
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| let func = | |
| ( | |
| TableWithTextColumn as table, | |
| ColumnNameTextToReplace as text, | |
| ReplacementsTable as table, | |
| ColumnNameWordToReplace as text, | |
| ColumnNameReplaceWith as text | |
| ) => | |
| let | |
| Source = ReplacementsTable, | |
| CreateListOfLists = Table.AddColumn( | |
| Source, | |
| "Liste", | |
| each ({Record.Field(_, ColumnNameWordToReplace), Record.Field(_, ColumnNameReplaceWith)}) | |
| ), | |
| Text = TableWithTextColumn, | |
| TurnTextToList = Table.AddColumn( | |
| Text, | |
| "Custom", | |
| each Text.Split(Record.Field(_, ColumnNameTextToReplace), " ") | |
| ), | |
| Replacements = Table.AddColumn( | |
| TurnTextToList, | |
| "Changed Text Expected", | |
| each Text.Combine(List.ReplaceMatchingItems([Custom], CreateListOfLists[Liste]), " ") | |
| ), | |
| Cleanup = Table.RemoveColumns(Replacements, {"Custom"}) | |
| in | |
| Cleanup , | |
| documentation = [ | |
| Documentation.Name = " Table.ReplaceMultiple.pq ", | |
| Documentation.Description = " Replaces multiple values at a time in a table column without recursion. ", | |
| Documentation.LongDescription = " Replaces multiple values at a time in a table column without recursion: http://www.thebiccountant.com/2016/05/22/multiple-replacements-in-power-bi-and-power-query/ The <code>ReplacementsTable </code> must have the values to be replaced in the 1st column and the new value in the 2nd. ", | |
| Documentation.Category = " Table.ColumnOperations ", | |
| Documentation.Source = " http://www.thebiccountant.com/2016/05/22/multiple-replacements-in-power-bi-and-power-query/ . ", | |
| Documentation.Version = " 2.0 ", | |
| Documentation.Author = " Imke Feldmann ", | |
| Documentation.Examples = {[Description = " ", | |
| Code = " let | |
| TableWithText = #table( {""Text""}, List.Zip( { {""the cat sat on the mat"" ,""the cat sat next to the dog"" ,""the dog chased the cat"" ,""the dog sat on the mat"" ,""the catamaran sails through the air""} } ) ), | |
| ReplacementsTable = #table( {""Word To Replace"", ""Replace With""}, List.Zip( { {""cat"" ,""mat"" ,""dog"" ,""the"" ,""air""}, {""bear"" ,""chair"" ,""dragon"" ,""THE"" ,""water""} } ) ), | |
| // The query containing the function has to be called ""fnTable_ReplaceMultiple_pq"" | |
| Result = fnTable_ReplaceMultiple_pq ( TableWithText , ""Text"", ReplacementsTable, ""Word To Replace"", ""Replace With"" ) | |
| in | |
| Result ", | |
| Result = " Changed Text Expected | |
| ——————— | |
| THE bear sat on THE chair | |
| THE bear sat next to THE dragon | |
| THE dragon chased THE bear | |
| THE dragon sat on THE chair | |
| THE catamaran sails through THE water "]}] | |
| in | |
| Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation)) |
Enjoy & stay queryious 🙂
Hi Imke 🙂
Very elegant solution 🙂
If you want to speed it up more, you may use List.Buffer function. I mean something like this
CreateListOfLists = List.Buffer(Table.AddColumn(Source, “Liste”, each ({[Word To Replace], [Replace With]}))[Liste]),
and then you have to change a little Replacements step to this below
Replacements = Table.AddColumn(TurnTextToList, “Changed Text Expected”,
each Text.Combine(List.ReplaceMatchingItems([Custom],CreateListOfLists),” “)),
Of course this is a proposal only, because your solution should works fine without these changes 🙂
From me, another approach (just different)
let
Repl = Table.Buffer(ReplacementsTable),
Text = Text,
Result = Table.AddColumn(Text, “Changed Text Expected”,
each Text.Combine(
List.Transform(
Text.Split([Text], ” “),
each
try Repl[Replace With]{List.PositionOf(Repl[Word To Replace], _)}
otherwise _
),
” ” )
)
[[Changed Text Expected]]
in
Result
sq 🙂
Hi Bill,
thanks for the accolade. Actually, I found my solution quite elegant as well – until you came around …
But yours is stellar, so I can comfort myself a bit in thinking that within earthly scale it still shines a little 🙂
Note to my readers: This cosmic M-agician shows us how to make multiple replacements without a dedicated replacement-operation but with the most elegant lookup-operation I’ve come across in M instead: No join-expand-if-then but simple try lookup otherwise old value. Also he returns a table with one column with this statement: [[Changed Text Expected]] which I still need my head to turn around, as it looks as if it turns a list into a table, but actually only seems to work on table columns and not on native lists. @Bill: Any idea why that is?
Apart from that: Thank you so much Bill and applause, this was a real experience!
And yes: List.Buffer speeds it up considerably! But I might actually prefer this version then:
let
Source = ReplacementsTable,
CreateListOfLists = Table.Buffer(Table.AddColumn(Source, “Liste”, each ({[Word To Replace], [Replace With]}))[[Liste]]),
TurnTextToList = Table.AddColumn(Text, “Custom”, each Text.Split([Text], ” “)),
Replacements = Table.AddColumn(TurnTextToList, “Changed Text Expected”, each Text.Combine(List.ReplaceMatchingItems([Custom],CreateListOfLists[Liste]),” “))[[Changed Text Expected]]
in
Replacements
Fan of double-brackets already :-))
Hello Imke,
I have read this post and tried to utilize this knowledge. Though I was able to set up the things in my model, there seems to be some items from my list doesn’t get replaced when I close in around 1000 rows of data. My data is not huge just below 3000 in total. Would you like to have a look at my file to help me out identify the issue please ?
Yes, please post link to file.
/Imke
Hi Imke,
I think I followed the logic above but I get the Error, Unexpected error: An item with the same key has already been added. Is there anything obvious I’ve done wrong here. You’r help is greatly appreciated. The steps work up to Replacements but then I get the error.
let
Source = Words,
CreateListOfLists = Table.Buffer(Table.AddColumn(Source, “Liste”, each ({[OldWords],[NewWords]}))[[Liste]]),
TurnTextToList = Table.AddColumn(Counts, “Custom”, each Text.Split([Description], ” “)),
Replacements = Table.AddColumn(TurnTextToList, “Changed Text Expected”, each Text.Combine(List.ReplaceMatchingItems([Custom],CreateListOfLists[Liste]),” “))[[Changed Text Expected]]
in
Replacements
Hi HD,
that’s probably because your “Counts”-table has a column named “Custom”.
So you either rename that or change the column name in my code (in step “TurnTextToList” and in step “Replacement”).
I tried replacing “Custom” with “unique” in both places but the error remains. The “Counts” table only had one column in it with a different heading.
I realized the error was because I had repeated a word twice in my Stopwords list.
Thanks for your help, it’s a great piece of code.
Thanks for your kind words, Imke. 🙂
To understand this syntax (double brackets), look at “6.4.2 Field Access” in Formula Language Specification (page 61)
Regards and sq 🙂
Hi Bill,
The Formula Language Specification (“6.4.2 Field Access”) is currently right in front my eyes.
Even knowing what you showed here about [[ ]], I would have never understood it from the documentation !!!
The only hint is the world “Table” briefly written down on the second line. I wonder how you figured it out.
Thanks Bill – looks like I need to learn some basics finally… (a list is missing the identifier and therefore cannot be selected or projected?)
sq 🙂
Simply Genius, Thank you for your post.
How do I go about getting the “Replace With” as my final result, i.e instead of particular ‘word’ replace, I would like the replace word to be the column result.
Text Word To Replace Replace With Expected Result
the cat sat on the mat cat bear bear
Thank you
Text = the cat sat on the mat,
Word To Replace = cat,
Replace With = bear,
Expected Result = bear
Just to make sure to understand your requirement correctly: You want to replace multiple words of whom one is “cat” with just one word “bear”?
That is Correct. Instead of the whole sentence- I would like the result to display just one word “bear”.
Thank you for looking into it.
This can be achieved by modifying the last step “Replacements” by this:
Table.AddColumn(TurnTextToList, "Changed Text Expected", each List.First(List.Select(CreateListOfLists[Liste], (Match) => List.Contains([Custom],Match{0}))){1})It will return the first match that is found in the list. If you want the last match instead, you simply replace “List.First” by “List.Last”.
Hope this helps and cheers, Imke
Hi! This is was super helpful – thank you so much for posting this. Is there is a way to replace the text directly in the original column instead of adding the new column “Changed Text Expected”? I’ve been trying to modify the code to do this with no such luck. Any insight would be greatly appreciated! Thank you.
Hi Ayla,
yes, this can be done like this: = Table.TransformColumns(Text,{{“Text”, each Text.Combine(List.ReplaceMatchingItems(Text.Split(_, ” “), CreateListOfLists[Liste]),” “)}})
Works with the data in the downloadable file.
Cheers, Imke
Thank you so much for the quick response! This is so great, thanks again! 🙂
Hi Ayla,
Where do I, exactly, have to change the code?
I am new in power query, but this function helped me a lot.
I have changed the chunk of code of your reply by =>
Replacements = Table.AddColumn(
TurnTextToList,
"Changed Text Expected",
each Text.Combine(List.ReplaceMatchingItems([Custom], CreateListOfLists[Liste]), " ")
)
Really thanks for your help!
Hi Ayla,
I am a newbie in power query, and i would like to replicate this change in the code. Where exactly do i have to replace it in the code?
I reallu appreciate your help 🙂
Hi there,
First, thanks a lot for this post and many others that have been super useful.
What I’m gonna ask may be really simple to extrapolate from your query but I haven’t been successful so far. Could I replace the values in my already-existing column, without using the split function so I replace full strings by another full string.
For example, I want to replace all cases where the value “Two words” is found by the value “Other three words”. There are obviously more values to replace on the same column.
Many thanks for your time,
Currently, I see only Chris’ method for this use case.
Do you see the risk that your “Two words” phrases will be included in other phrases as will and you’ll actually run into the problems I’ve described?
Hi!
Thanks for quick reply. I did understand the problems of Chris´method, that’s why I opted to follow this solution. I made a few changes to your query to make it more robust and it works fine.
There is a couple of cases I have to check first and, if it works for all of them, I’ll let you know!
Regards,
NAOS
That sounds great! Very much looking forward your solution.
Thanks and cheers, Imke
Pingback: Transform text with exceptions in Power BI and Power Query – The BIccountant
Hi,
Thanks a lot for all the examples and explanations.
I’m trying to exchange whole text string values in a column. The problem I have is that it returns the whole list for every record. What do I wrong? I just want to have the exchanged value in the column.
#”CAL freq list ” = Table.AddColumn(#”Replaced Value1″, “Updated freq”, each List.ReplaceMatchingItems(#”Replaced Value1″[#”CAL freq.”], Frequencies))
Frequencies is a list with search value and replace value. {search value 1, replace value 1},{{search value 2, replace value 2}, …..
Kind regards,
Christiaan
Hi Christiaan,
try omitting the table name:
#”CAL freq list ” = Table.AddColumn(#”Replaced Value1″, “Updated freq”, each List.ReplaceMatchingItems(
#”Replaced Value1″[#”CAL freq.”], Frequencies))If this doesn’t work, please paste the full code.
/Imke
Hi everyone, this is great! Thanks for the post.
I’m having trouble getting a string with multiple words to be replaced properly.
For example, my transformation table has:
Business Development –> BD
Marketing –> MKTG
Medical Affairs –> CDMA
and my [Column] (using Imke’s example) has strings such as:
Business Development;Marketing;Medical Affairs
Marketing
Medical Affairs;Marketing.
The result I’m getting is:
Business Development;MKTG;Medical Affairs
MKTG
Medical Affairs;MKTG
which leads me to believe the space inbetween the two words to be replaced is an issue. I have tried removing that/trimming text, etc., to no avail.
Any suggestions?
Hi tcp,
which delimiter are you using in Text.Split(text, ” “) ?
I’ve used the space, but for your case, you’d have to juse “;”.
/Imke
Yes, of course! I was mistakenly replacing the space in Text.Combine(List.ReplaceMatchingItems([Custom],CreateListOfLists[Liste]),” “)).
Thanks 🙂
How do i use this query in a merged query where already many steps are carried out and initial source file is different?
Hi, Thanks for the great post – this looks exactly like what I need. However, I am getting the following error on the line “Text = Text” when trying tio use the function in Power BI Desktop:
“Expression.Error: The import Text matches no exports. Did you miss a module reference?”
I have only changed the ReplacementTable and field references in teh ReplacementTable. Please could someone explain what the purpose of the “Text = Text” line is and what I need to do to get it working??
Here is my code:
let
Source = #”Fuzzy Transformations”,
CreateListOfLists = Table.AddColumn(Source, “List”, each ({[From], [To]})),
Text = Text,
TurnTextToList = Table.AddColumn(Text, “Custom”, each Text.Split([Text], ” “)),
Replacements = Table.AddColumn(TurnTextToList, “Changed Text Expected”,
each Text.Combine(List.ReplaceMatchingItems([Custom],CreateListOfLists[List]),” “)),
Cleanup = Table.RemoveColumns(Replacements,{“Custom”, “Text”})
in
Cleanup
Hi Ron,
Text has to reference the table that contains the words to replace. The column in there also has to be named “Text”.
Otherwise you’d have to adjust this code accordingly: TurnTextToList = Table.AddColumn(Text, “Custom”, each Text.Split([Text], ” “)),
The Text in square brackets stands for the column name and that Text without brackets stands for the table itselft.
/Imke
Regarding the Text = Text statement, according to what I understand, the left “Text” is the name of the table and the right “Text” is the name of the query that contains the table (and also the name of the column :-)).
You should then change the text 😉 accordingly.
but, it (the Text=Text) is not strictly necessary if, in the following statement, you use the name of the query containing the table instead of “Text”
Table.AddColumn (NameQueryContaningTable, “Changed Text Expected”, …
Hi, I’m a beginner with Power Query, but I like it a lot even if it’s not easy to find out useful documentation. I was lucky enough to come across this interesting blog and follow the discussion and learn the remarkable potential of the language.
I would be pleased to submit the following variant (which uses a sort of dictionary) to the brilliant solutions you have above illustrated.
let
Dict = Record.FromList( Source[Replace With],Source[Word To Replace]),
TurnTextToList = Table.AddColumn(testo, “Custom”, each Text.Split([Text], ” “)),
Replacements = Table.AddColumn(TurnTextToList, “Changed Text Expected”, each Text.Combine(List.Transform([Custom],each Record.FieldOrDefault(Dict,,)),” “))//[[Changed Text Expected]]
in
Replacements
You’re welcome!
I am using this function to try to clean a list of roughly 20,000 scandinavian company names. My replace column is mostly blanks because I am removing ” OY” and “AB ” etc. A number of the rows have numbers. Is this why I could be getting a PowerBI expression error of “We can not convert a value of type List to type Table. Details Value = [List] Type = [Type].
Or is this happening perhaps because in lieu of [Text] I am referencing a table[columnname] such as CustomerNames[Names]?
Imke is the best powerBI power genius! Thanks, IMKE
PS – this error happens at the TurnTextToList row
Thanks Charles 🙂
This probably means that the step “Text” in the row before returns a list instead of a table.
It must be the table with the text to replace in the column “Text” in it.
/Imke
@Imke – that edit fixed the issue. Thank you.
Question: how would you edit this script to make it a string / character replacement for the following situation, where currently I’m not getting replacements with this script.
Here’s the usecase: I have a table of company names that I want to normalize. Let’s say the company names are mostly German (although in reality they are mostly Scandinavian, which is a tougher normalization assignment!):
These are the names to normalize:
Names[Text] // ie Table called Names, Column to normalize called Text
Volkswagen, Inc.
Volkswagen GMBH
Daimler AG
Daimler KG
Robert Bosch GmbH
KG Daimler
SAP SE
SAP AG
Mercedes
AGFA-Gevaert Group AG
The ReplacementsTable would focus mostly on characters to replace:
//Note the spaces needed for clean replacement. AG below is ‘ AG. KG is KG ‘
//Also note that many of the Replace With fields are blank because we want those characters replaced by nothing — just deleted.
To Replace Replace With
, Inc.
GMBH
AG
KG
GMBH
GmbH
KG
SE
AG
Mercedes Daimler
The end result would be removal of the company signifiers to normalize the names.
Volkswagen
Volkswagen
Daimler
Daimler
Robert Bosch
Daimler
SAP
SAP
Daimler
AGFA-Gevaert Group //AG not replaced because string above was ‘ AG (space before AG)
I currently do this with a massively long find value and replace M query script. How would Imke master this? Is it worth trying to extend your formulae to this use case?
In worshipful reverence!
Charlie
Hi Charles,
for my understanding, it should work with the replacement of the empty values like you’ve described.
What am I missing?
/Imke
Oops I thought I’d posted a detailed response / analysis but it doesnt look like it went through. Will redo soon
Hi Imke,
This is great. What if i need to replace a phrase, e.g. the cat -> A BEAR. Chris’s solution can handle that, but has the recursive issue – chwater 🙁
Thanks,
Hi Di,
the solution for this depends on the characters that surround the phrase you’re searching: Is it always a blank/space or could it be signs like parenthesis, comma, colon and so on?
Are you able to specify or give a list of possible characters?
/Imke
Hi Imke,
Totally understand your logic now depends on delimiter e.g. space to split text to list, then compareing 2 list. What I’m looking for is not split the text (similar logic like Chris by using Text.replace because no rule to split) but fix the issue of chwater.
So just use exact sample data from Chris, but change the 1st record to “the cat” => “A BEAR”. I would expect result of all “the cat” replaced by “A BEAR’.
Thanks, Di
BTW, i think my request is not making sense since there is no way to tell which word to stop replacement. Please ignore it.
Hi Imke, is there a simple way to adapt this approach to pad each delimited value with leading zeros in order to correctly (alpha) sort a column with e.g. 1.10.2.1. I would like to pad each delimited value with 3 zeros e.g. 001.010.002.001.
Thanks, Brent
Hi Brent,
that’s an interesting challenge, but I don’t have time for it now unfortunately.
I have a function that pads one column dynamically based on the maximum value here: https://gist.github.com/ImkeF/187e5c3840738c70276810c4ad2fef4b
Hopefully it gives you a starting point to work it out by yourself.
Cheers, Imke
It seems the code is hardcoded to “Text” column name. I modified the last few lines of the m code to handle this:
// Had to add/modify the next two lines to use ColumnNameTextToReplace instead of hardcoded “Text” column name
#”Removed Columns” = Table.RemoveColumns(Replacements,{ColumnNameTextToReplace, “Custom”}),
Cleanup = Table.RenameColumns(#”Removed Columns”,{{“Changed Text Expected”, ColumnNameTextToReplace}})
in
Cleanup ,
Pingback: New M function: Text.ReplaceAll – XXL BI
Pingback: PQ text replacement competition – XXL BI
Thank you for this solution. How could I make it to that it replaces the full string?
let
Source = ReplacementsTable,
CreateListOfLists = Table.AddColumn(Source, “Liste”, each ({[Word from], [Word to]})),
Text = OLM[Department],
TurnTextToList = Table.AddColumn(OLM, “Custom”, each Text.Split([Department], ” “)),
Replacements = Table.AddColumn(TurnTextToList, “Changed Text Expected”, each Text.Combine(List.ReplaceMatchingItems([Custom],CreateListOfLists[Liste]),” “)),
Cleanup = Table.RemoveColumns(Replacements,{“Custom”, “Department”}),
#”Changed Type” = Table.TransformColumnTypes(Cleanup,{{“Changed Text Expected”, type text}})
in
#”Changed Type”
Thank you
Hi Lebene,
if you want to replace full strings, I strongly recommend to do a standard merge with the replacements table.
That doesn’t require individual code and should also be the fastest option.
/Imke
Thank you Imke, I came to this conclusion as well as I searched further (Chandoo had the perfect solution I needed) but definitely bookmarked this for future need.
Hi Imke,
thank you for this approach! Is it possible to replace only words from a related table?
I have a table with a “Document Id” column and a “TextToReplace” column. And I created a Table with a related “Document Id” column and the columns “WordToReplace” and “ReplaceWith”. But this query should not replace words, if the document Ids are not matching. Is this even possible with Power Query?
[TableWithTextColumn]
Document Id | TextToReplace
1941234719 | Hello World
7543234719 | Another Hello World text
[ReplacementTable]
Document Id | WordToReplace | ReplaceWith
1941234719 | World | Imke,
1941234719 | Another | The BI Accountant
7543234719 | Another | Thank
7543234719 | Hello | you
7543234719 | World | for
7543234719 | text | your help
[ExpectedResult]
Document Id | ReplacedText
1941234719 | Hello Imke,
7543234719 | Thank you for your help
Thank you and best regards
Johannes
Hello, thank you for this it is what I have been looking for
I am using this
let
Repl = Table.Buffer(ReplacementsTable),
Text = Text,
Result = Table.AddColumn(Text, “Changed Text Expected”,
each Text.Combine(
List.Transform(
Text.Split([Text], “|”),
each
try Repl[Replace With]{List.PositionOf(Repl[Word To Replace], _)}
otherwise _
),
“|” )
)
[[Changed Text Expected]]
in
Result
where I added a pipe delimiter
Is it possible to add a trim at this point?
Text.Split([Text], “|”),
I have strings that look like
the|cat sat |on the mat
find replace
cat sat >> bear
does no get replaced because of the space after the sat in “the|cat sat |on the mat”
I found this and it works
Text.Split(Text.Replace(Text.Replace([Text],” |”,”|”),”| “,”|”) “|”)
Use abovein place of Text.Split([Text], “|”)
Hi. What if I can’t use a delimiter? Since I want to find “Won T” within a phrase and replace it with “Won’t”.
Hello. I’m a COMPLETE newbie to Power Query and M.
I tried using the function and am getting an error:
An error occurred in the ‘ReplaceMultipleValues’ query. Expression.Error: We cannot convert the value “CleanupZonas” to type Table.
Details:
Value=CleanupZonas
Type=[Type]
I have three tables.
Source with names to normalize has several columns and including the following. The names to normalize are in a table named ‘Data’ and look like this:
Product Place of Origin
1 Cordoba, Argent
2 Buenos Aires
3 Sta.Fe.
4 Santa Fe, AR 200
5 Buenos Aires – Argentina 200
6 Campana, BS.AS.
7 Avellaneda,BS.AS.
8 Cordoba – Argentina
The targetColumnName is ‘Place of Origen’
The ReplacementsTable, a table named CleanupCities, mostly has characters to replace:
Text ReplaceWith
, Argentina “”
,Argentina “”
– Argentina “”
,AR 200 “”
Sta.Fe. Santa Fe
– Argentina 200 “”
,BS AS , Buenos Aires
,BS.AS. , Buenos Aires
// Note that their are spaces needed for clean replacement.
// Many of the Replace With fields are blank because we want those characters replaced by nothing.
The desired output is:
Product Place of Origin
1 Cordoba
2 Buenos Aires
3 Santa Fe
4 Santa Fe
5 Buenos Aires
6 Campana, Buenos Aires
7 Avellaneda, Buenos Aires
8 Cordoba
I am setting the following:
originalTable: Data
targetColumnName: Place of Origen
valuePairs: CleanupCities – I tried entering ‘CleanupCities’ and ‘[CleanupCities]’ with the same result.
It’s probably an error with my syntax but I can’t figure it out.
Thanks in advance for your help.
Hi
I get an error when replacing words. I would like to ask for help.
https://drive.google.com/file/d/1PwBxLm_FF1LQx1I1RClYfxv5x-u0Daiq/view?usp=sharing
This solution works on a small sample I created. Thank you for the functionalized version of the query code as well!
Would it possible for you to improve this code so that it is not case sensitive? As in replace “Air”, “air” or “AIR” with “water”?