How to do multiple substitutions in the same step?

[matheus_peppers]

I am trying a challenge to do multiple substitutions in the same column, but only consume a single step, instead of having several.

Example:

I want to do the following substitutions in the column:

Valor Atual	Valor Desejado
instagram_stories	stories
instream_stories	instream stories
instream_video	instream video
unknown	desconhecido

And furthermore, I want to do the same thing in another column that I want to apply multiple replacements like this in the same step.

Can anyone help me?

[jbwtp]

Hi @matheus_peppers,

I guess there are few options depending on your preferences.

This one uses a translation list to do the substitutes, you can also add some default value to List.First which would be returned if the item is not on the list (e.g. return x which is the original value).

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WyswrLklML0rMjS8uyS/KTC1W0lGCsWJ1IPJFqSjSMCEFrOrKMlNS85FVQQRAakrzsvPyy/OAkimpxcn5eRmpyZkpQKlYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Valor Atual" = _t, #"Valor Desejado" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Valor Atual", type text}, {"Valor Desejado", type text}}),
    translations = {{"instagram_stories", "stories"}, {"instream_stories", "instream stories"}, {"instream_video", "instream video"}, {"unknown", "desconhecido"}},
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Valor Atual", "Demo"),
    #"Duplicated Column1" = Table.DuplicateColumn(#"Duplicated Column", "Demo", "Demo - Copy"),
    #"Renamed Columns" = Table.RenameColumns(#"Duplicated Column1",{{"Demo - Copy", "Demo - 2"}}),
    #"Replaced Value" = Table.ReplaceValue(#"Renamed Columns",null,null,(x,y,z) as text => List.First(List.Select(translations, each _{0}=x)){1},{"Demo", "Demo - 2"})
in
    #"Replaced Value"

Kind regards,

John

[matheus_peppers]

Sorry buddy, I didn't understand it very well. Especially since I am very new to the M language. What would be the simplest way for me to do this? I tried to use List.ReplaceMatchingItems, but it didn't work. It says something like: "5 arguments have been entered for something that would expect 2 or 3 arguments". I don't get it, very well.

[jbwtp · Accepted solution]

Well, the bits that you need above are:

translations = {{"instagram_stories", "stories"}, {"instream_stories", "instream stories"}, {"instream_video", "instream video"}, {"unknown", "desconhecido"}},

This is a vocabulary of substitutions, what do you want to replace with what. You would need to add it manually (using Advanced Editor) between any lines (as long as it is not after the last one [before the last "in"). And of course you will need to add your pairs using the provided template/example above.

#"Replaced Value" = Table.ReplaceValue(#"Renamed Columns",null,null,(x,y,z) as text => List.First(List.Select(translations, each _{0}=x)){1},{"Demo", "Demo - 2"})

This line is what actually does the job, all you need to do here: change the #"Renamed Columns" to a reference to the step in your code that should preceed the replace.

Actually, I suggest that you would use the stock Replace command from menu on the columns that you want to replace values and then change the fourth parameter (Replacer.ReplaceText ) to (x,y,z) as text => List.First(List.Select(translations, each _{0}=x)){1} it should solve the problem.

Cheers,

John