Multiple table.replacevalue

[tobermory]

Hi,

So embarrassed to ask this, new to PQ. Soon as I see the answer it'll click.

I have three Table.ReplaceValue, would like to merge into one step.

= Table.ReplaceValue(#"Changed Type","Ad","Adult",Replacer.ReplaceText,{"Column1"})
= Table.ReplaceValue(#"Replaced Value","Ch","Child",Replacer.ReplaceText,{"Column1"})
= Table.ReplaceValue(#"Replaced Value1","Sr","Senior",Replacer.ReplaceText,{"Column1"})

I know it involves { and } but cant seem to get the right combo.

Thanks very much.

 

[JGordon11]

Probably some better ways exist. But here are two options:

1) You can nest the three Table.ReplaceValue functions succinctly using let..in

= let f = Table.ReplaceValue, r = Replacer.ReplaceText, c = {"Column1"} in f(f(f(#"Changed Type","Ad","Adult",r,c),"Ch","Child",r,c),"Sr","Senior",r,c)

as in:

let
    Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    Out = let f = Table.ReplaceValue, r = Replacer.ReplaceText, c = {"Column1"} in f(f(f(#"Changed Type","Ad","Adult",r,c),"Ch","Child",r,c),"Sr","Senior",r,c)
in
    Out

2) or you could use Table.TransformColumns with a custom transform operation, but as written this only works if the Column just contains the "Ad" and not something like "Price for Ad"

= Table.TransformColumns(#"Changed Type",{"Column1", (x) => if x = "Ch" then "Child" else if x = "Ad" then "Adult" else if x = "Sr" then "Senior" else x})

or

= Table.TransformColumns(#"Changed Type",{"Column1", (x) => let lpo = List.PositionOf({"Ch","Ad","Sr"},x) in if lpo>=0 then {"Child","Adult","Senior"}{lpo} else x})

as in:

let
    Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    Out = Table.TransformColumns(#"Changed Type",{"Column1", (x) => if x = "Ch" then "Child" else if x = "Ad" then "Adult" else if x = "Sr" then "Senior" else x})
in
    Out

 

[JGordon11]

Another way using a custom m function called Table_ReplaceColumn:

let
    Table_ReplaceColumn = (tblInput as table, txtColName as text, lstColRepl as list) as table =>
        let
            lstTable = Table.ToColumns(tblInput),
            tcn = Table.ColumnNames(tblInput),
            col = List.PositionOf(tcn, txtColName),
            lstColumns = List.FirstN(lstTable,col) & {lstColRepl} & List.Skip(lstTable,col +1),
            tblOut = Table.FromColumns(lstColumns,tcn)
        in
            tblOut,
    
    Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    lstChanges = {{"Ch", "Child"},{"Ad","Adult"},{"Sr","Senior"}},
    tblResult = Table_ReplaceColumn(Source,"Column1",List.ReplaceMatchingItems(#"Changed Type"[Column1], lstChanges))
in
    tblResult

 

[XL Pro]

Here are two other alternatives, one using if / else if and another using a Record as a type of Switch function:

if/else if:

= Table.ReplaceValue(#"Changed Type",each [Column1], each if [Column1] = "Ad" then "Adult" else if [Column1] = "Ch" then "Child" else if [Column1] = "Sr" then "Senior" else [Column1],Replacer.ReplaceText,{"Column1"})

Record as Switch Function:

= Table.ReplaceValue(#"Changed Type",each [Column1], each Record.FieldOrDefault([Ad = "Adult", Ch = "Child", Sr = "Senior"], [Column1], [Column1]),Replacer.ReplaceText,{"Column1"})

In the Record.FieldOrDefault:
1st Parameter: A record with the "Conditions",
2nd Parameter: Pass the criteria (ie, the value in Column1)
3rd Parameter: Default value (ie, pass Column1 again and return that value if there is no match)

Personally I like the Record/Switch procedure as it's easier to add additional conditions