Iterate Columns in a List of Tables Replace Values with a table of From and To

[Ninshu]

Hello, I am having a hard time wrapping my head around this but I would love to understand how it can be done,

I would like to use a reference table with a From and To column to replace values in each column of a list of tables.

I am probably overthinking it, but I was attempting to use List.Accumulate which I have used before for a single column of a table as shown below.

#"Added Custom" = 
Table.AddColumn(#"Cleaned Text", "Cleaned Text", each  
    List.Accumulate(List.Numbers(0, Table.RowCount(Transform)), 
    [Column1],(state, current) => 
            Text.Replace(State,
                     Transform[From]{Current}, 
                     Transform[To]{Current}))

Is it possible to use Table.ReplaceValue in this scenario? I have tried a number of things that I can think of to get this working.

The code block below is a mess sorry in advance 🙂

#"Added Custom" = 
 Table.AddColumn(#"Grouped Rows", "Replace", 
    
    each
    Columns = Table.ColumnNames(
                Table.FromList([All]{0}, 
                Splitter.SplitByNothing(), null, null, 
                ExtraValues.Error
                )
            ),
    List.Accumulate(
        List.Numbers(0, List.Count(Columns)
        ),

    Columns, (LoopState, CurrentColumn) =>
    Table.Columns(Columns, CurrentColumn),
    each List.Accumulate(
        List.Numbers(0, Table.RowCount(Transform)
    ),
            Columns{CurrentColumn}, (State, Current) => 
               Table.ReplaceValue(State, 
                    Transform[From]{Current}, 
                    Transform[To]{Current}, 
                    Replacer.ReplaceValue, Columns)
        ))
in
    #"Added Custom"

[KT_Bsmart2gethe · Accepted solution]

HI @Ninshu ,

Silly me, I missed out on one important syntax in the Table.ReplaceValue that caused the error.

I tested the code below and it works.

Code:

List.Accumulate(
{0..Table.RowCount(Transform)-1},
#"Grouped Rows",
(LoopState, CurrentColumn) =>
Table.ReplaceValue(
x,ReplacementTbl[From]{CurrentColumn},
ReplacementTbl[To]{CurrentColumn},
Replacer.ReplaceText,
Table.ColumnNames(#"Grouped Rows")
)
) 

[Ninshu]

This is what worked in my situation.

I had a table full of values like 1-b-5, 4-a-1 stuff like that and each value was supposed to represent a word so 

1 = fish, b=customer

I cant say exactly what they stood for in my senario but either way you helped me replace 12 columns of that with actual words thank you for your help, in your example you referred to a previous step I honestly didnt think to do that. I will consider that in the future. M has been interesting to learn keep up the good work! I will be using this in the future its quite dynamic. 🙂

    #"Added Custom" = 
    Table.AddColumn(#"Grouped Rows", "Replace", each List.Accumulate(
      {0..Table.RowCount(Transform)-1},
      [All],
    (State, Current) =>
      Table.ReplaceValue(
               State,
               Transform[From]{Current},
               Transform[To]{Current},
               Replacer.ReplaceText,
               Table.ColumnNames([All])
               )
    )),

[KT_Bsmart2gethe]

Hi @Ninshu ,

Let me know if below proposed code help.

Below is a mock-up data based on your description:

Replacement Table:

Result:

Code:

let
Source = Excel.CurrentWorkbook(){[Name="MainTbl"]}[Content],

#"Added Custom" =
Table.AddColumn(Source, "New Column", each
List.Accumulate(
List.Numbers(0,Table.RowCount(ReplacementTbl)),
Record.ToList(_),
(x,y) =>
List.ReplaceValue(x,
ReplacementTbl[From]{y},
ReplacementTbl[To]{y},
Replacer.ReplaceText)
)
),

#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"New Column"}),
#"Extracted Values" = Table.TransformColumns(#"Removed Other Columns", {"New Column", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "New Column", Splitter.SplitTextByDelimiter(";", QuoteStyle.None))
in
#"Split Column by Delimiter"

Regards

KT

[KT_Bsmart2gethe]

Hi @Ninshu ,

I am trying to visualize the code; however, there are codes I don't quite get understand and I like to clarify with you.

1. [All]{0}   (will this return something?)

 Table.FromList([All]{0}

2. Have you tried List.ReplaceValue?

Table.ReplaceValue(State,

Are you able to merge all the columns into one, use your single column's code to perform the find and replace then split the column?

Regards

KT

[Ninshu]

Hello,

Thank you for the reply sorry it took me awhile to get back to you, I am attempting to replace values in multiple columns with a custom function.

The main function setup I found information on was using List.Accumulate with List.ReplaceValue. This is limited by the fact that you cannot do more than one column. So I wanted to try doing List.Accumulate and Table.ReplaceValue.

I also have been experimenting on working with a nested table. So the function is referring to a list of tables. 

For Comparison:

List.ReplaceValue

List.ReplaceValue(list as list, oldValue as any, newValue as any, replacer as function) as list

Table.ReplaceValue

Table.ReplaceValue(table as table, oldValue as any, newValue as any, replacer as function, columnsToSearch as list) as table

 So the second function needs column names, I want to be able to dynamically get the column names from the nested table. I had Table.FromList([All]{0} because I gave up trying to get the columns from each table in the list of tables and was selecting the first table in the list to get the column names from.

Hopefully I didnt make this more confusing 🙂

[KT_Bsmart2gethe]

Hi @Ninshu ,

All good.

When you apply List.Accumulate() the list pointed to a column or a row, which means it replaces the range of values in the column or a row with the value from the replacement table if the condition is met. In a simple word, it performs a single dimension find and replace. 

The challenge with Table.ReplaceValue is the seed is "Source" (i.e. containing columns and rows. two dimensions). I can see you attempted with Table.Column(). 

My attempt to replace the value from a list is through row search instead of columns. Let me have another go see if I could figure out how to use List.Accumulate() to do two dimensions find and replace.

I attempted code below but return error. I will have another go:

List.Accumulate(

      List.Numbers(

            0,

            Table.RowCount(ReplacementTbl)

            ),

      TableName,

      (x,y) =>

      Table.ReplaceValue(

               x,

               ReplacementTbl[From]{y},

               ReplacementTbl[To]{y},

               Table.ColumnNames(TableName)

       )

)

Regards

KT