Replace part of text in field in one step

[Gaelan]

I have a field of text (in this case, manually entered dates) and there are typos and other languages. I need to replace part of the text field - exactly what ReplaceText does, only I have a fairly long list and want to condense it to one step.

The only methods I can find replace the entire field, not part of it. The first is creating the list of Substitutions in one step and then making the change in the next, like this:

Substitutions = [
        #"mag" = "May",
        #"giu" = "Jun",
        #"lug" = "Jul",
        #"sept" = "Sep"],
Substituted = Table.TransformColumns(Source, {{"ColumnOfDates", each Record.FieldOrDefault(Substitutions, _, _)}})

I found another solution in this video that looks so close to what I want, but still replaces the entire field. It takes the normal ReplaceText and breaks it up to add an IF statement

= Table.ReplaceValue(#"LastStep",
each [ColumnOfDates],
each if ~some clause~ then "replacement text" else [ColumnOfDates],
Replacer.ReplaceText,{"ColumnOfDates"})

I don’t know how to manipulate either of these to only replace part of the field, not the whole field. Here is the sample file, with all the replacement values in individual steps.

[Melissa]

Hi @Gaelan,

I overlooked your file. But on second look found it
Give this a go:

let
    Replacements = {{"mag","may"}, {"giu","jun"}, {"lug","jul"}, {"set","sep"}, {"gen","jan"}, {"févr","feb"}, {"avr","apr"}, {"juin","jun"}, {"mrt","mar"}, {"sept","sep"}, {"يونيو 08, 2022","June 08, 2022"}, {"نوفمبر 21, 2021","nov 21, 2021"}, {"أبريل 12, 2022","apr 12, 2022"}},
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("5VvNahwxDH6VIecQbO9Pdo6B0kMg7aHHkEMalkBp0rB0Cz0W2hL6Gr302rfJvk3X2tmuLHks2+P9SeZS6JexLcuy9EnyXl4evZs+VMocV0YZc3R1fHl0Nr+tjLaArgE4n3+s9Ah9cX59X2mFADuHHofmYMDF9dflf2CIihxigYFOWsVKqgZolUxAD8hu1WmSHBbQNZr04nrGt+98ETfEDJPmsKKbleg6QQ5Vk93yL8YSQE5/uSw2qdfT92ut9xE4e1jqo0bnsic57K3UtWMfuwAO5RieDwDuc0J02mPAKqjxhSYXePPpS+O2Gi8lA+fzZbDQSI4MAPwpDcCJgJ3UiVERodFGdTpEO4HPTirGOTLEMymLyAZP+owBtn1Z6xCAMXWCmz2ivnD3QByHabbfsltrMM2tNMUA0NgwSVKgcOygGMtJZ58vFgB/igk8A0p4qUwgnIwcigr3dlCzdeql2k+Oe+0Ct4HPMUkHOl5Caw4DRbxDYpIYuVsqesYXLBSsokeSHGpINlfCj61U2Fsggikxl1PTIb0CHKMDYqSIgsIe5u3NZ5dM9AuI86fcLFm1R3bj6UNKAMlhbVYZzKRfTW/WFA6xvhFdpQQgSxqcg4nOgFJyOEq2CmpWWSlo8WvxuPhp/60U5s+lWEfXGg/LfLYC2JumsOj9AuJYh8LnAgmXIQflfPH0++nP09+lZX13uTljmrJ8UEvvyM4gHAery54h925/IgOwJdoGkC8cq4+H5Wubats4JA/DiA+tzp02AQAGAbY0pjFLAAAzYgiCaedm5VNpZa2l9I+Lb4sf1mSbv6zPa/9/STwepmR7NR3yyr4AfdCSpWONDNhdJstqZXLxTB6SMeme9rJ9gPUuvS468RLGLWtwMRG5+ZaaHTNcJin7wm/r4qQK8+4P0LmuAg4FQhbuLDCg2EEFq1igU03uvqJkPrwKdDjwHAwAw6WJyW6AcFkrici0THo7tWXgE5ewqEHSUTKNsWXhi7TKD9KH7ilQXEFw95lPTluWXbGMO+c3bXzV/bV26mESLxD7AtlpSkfQybmh3TmkZALburclFuH6Tskc/QH8jp6yvnAG7623laBOOSyHkgl5SDjwlQprzhAoJ41Dk0IlIagPq3WnsyADcTqVc1dHMMa2/JPSNkE43oL7rMmkXDDGHNOBEox+DwBzfbC5UegL75AwAB53IrlPx9YjgPn/55q6HTC1Yw7JQFx8kVu3OlgYZS9NMwA5eGYG4MSDypi0xDMnGfC4i5n7EogBsug2j9K4zhjh+R9m66dSpgUokEiw18wQgOmrWTeqb0K02ggWVKH8zNpv6zhdYck7IgItOoVmxNDZ3P265Kc3oRFvzpqDc7YykBkaw7cyY1KksViAlQjkcwGNDYmkStyL2UbRTWFvyV4BZgAZcsDNHlFbL9CDku2juE79YX4rBUTWmD2AAuJugDuow6DCDBhMkMCX4nRyeHXiSwZwZ0OjPukEyJKyOGd1qs1JJ4D/0Ccd8OuUFt10sI6LesfZhTvrlAZBIMNwURBXfsA/R9rz1YyskfnCrQWcIq6v4zWNq9OFV7FBXOOKQAbgyXCWRGCCbhQvmIldDd7msNxyTL/AzVh5FWCODlGkk0akPHSIzLWBWp/SIf19vRuX8jivDLayCns1631G23UIyis3d84E3Tg0/Gmi+WKBiPz2+QCHcoEOBGCvu3cHqGAdF5JVJ+DQ7FVutHu9g9ys79rTymVsmEruCfAztmCBWU5FUT0ocNiGHjYDuDmI9Fz+sRAPa9hd+CtoTs2gVwBwy2A0LQGwFxEsIgODpc7AuYSH8oNFGUCPbJbA1T8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Modified Date" = _t]),
    Replacer = Table.ReplaceValue(Source,each [Modified Date], each 
        if List.Contains( Replacements, [Modified Date])
        then List.ReplaceMatchingItems( [Modified Date], Replacements, Comparer.OrdinalIgnoreCase )
        else Text.Combine( List.ReplaceMatchingItems(Text.Split([Modified Date], " "), Replacements, Comparer.OrdinalIgnoreCase ), " "),
        Replacer.ReplaceValue,{"Modified Date"}
    )
in
    Replacer

it consolodates all except the #“Replaced Value4” step
I hope this is helpful.

[Gaelan]

This is fantastic, thank you! It is taking forever to load, but it works!

Can you walk me through this? I can’t parse out what you’ve done in the last step.

1. Make a list, Replacements
2. If that list, Replacements, contains [Modified Date] …
3. Then put the Replacement in ← is this to check if the entire [Modified Date] is in the Replacements list? Do you add this instead of just using the last bit to speed up what can be sped up? What if you just skipped to the last step?
4. Else… here’s where I can’t figure out what’s going on.

Also, it looks like I’ll need to do the punctuation and extra spaces in separate steps? Is that because of how the text splitter is working?

Thanks again!

[Melissa]

See if buffering Replacements, helps performance.
I’ve added comments in the image above, your initial thoughts are correct.

In the “else” part, the string has to be broken apart because we want to do a partial replacements. Text.Split returns a list. We can replace each item in that list similar to the “then” part. in the end we want to return a string and not a list with items - that’s where Text.Combine comes in also adding back the space on which the string was initially split.

I hope this helps.

[Gaelan]

This is fantastic and exactly what I was looking for. It makes so much sense now.

Thank you again!