Forum Discussion

DanFromMontreal's avatar
DanFromMontreal
Helper IV
4 years ago
Solved

Replace values in different columns using a table (tblBeforeAfter)

Hello all,

I'm refining my knowledge of the PowerM language and I find that I have redondant code that I'm sure could be simplified... only if I knew how.

Tried many approach but I still get error.

Here is my challenge.

I have to transform a file by replacing the English word by its French word in 4 different columns.

Tried different technique.  The two first are working but some code are redondant.

What if I had to do it not on 4 but 15 columns????

What if I had to replace not 4 but 20 words???

The code would become cumbersome / heavy.

A table where I could easily Add/Remove word is the ideal solution I think.

Tried to modify some of the code in (3) to ressemble like (1) but could not make it work.

The question is:  How to modify (2) to make it work for multiple columns at once????

Any suggestion?

Thank you for making me write better PowerM code

Daniel

 

// (1) Replace using Replace.Value on multiple Columns at once (working)

let

    Source = Csv.Document(File.Contents("C:\NC20220410.csv"),[Delimiter=";", Columns=45, Encoding=65001, QuoteStyle=QuoteStyle.None]),

    #"En-têtes promus" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),

    #"Replace Yes-Oui" = Table.ReplaceValue(#"En-têtes promus","Yes","Oui", Replacer.ReplaceText,{"ColumnA","ColumnB", "ColumnC", "ColumnD"}),

    #"Replace No-Non" = Table.ReplaceValue(#"Replace Yes-Oui","No","Non", Replacer.ReplaceText,{"ColumnA","ColumnB", "ColumnC", "ColumnD"}),

    #"Replace Maybe-Peut-être" = Table.ReplaceValue(#"Replace No-Non","Maybe","Peut-être", Replacer.ReplaceText,{"ColumnA","ColumnB", "ColumnC", "ColumnD"}),

    #"Replace Of Course-Certainement" = Table.ReplaceValue(#"Replace Maybe-Peut-être","Of Course","Certainement", Replacer.ReplaceText,{"ColumnA","ColumnB", "ColumnC", "ColumnD"})

in

    #"Replace Of Course-Certainement"

 

// (2) Replace via table on single Column at a time (working)

let

    Source = Csv.Document(File.Contents("C:\NC20220410.csv"),[Delimiter=";", Columns=45, Encoding=65001, QuoteStyle=QuoteStyle.None]),

    #"En-têtes promus" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),

    #"ReplaceViaTableColumnA" = Table.TransformColumns(#"En-têtes promus",{ "ColumnA", each Text.Combine(List.ReplaceMatchingItems({_},List.Buffer(List.Zip({tblBeforeAfterLight[Before],tblBeforeAfterLight[After]})))), type text } ,null,MissingField.Ignore),

    #"ReplaceViaTableColumnB" = Table.TransformColumns(#"ReplaceViaTableColumnA",{ "ColumnB", each Text.Combine(List.ReplaceMatchingItems({_},List.Buffer(List.Zip({tblBeforeAfterLight[Before],tblBeforeAfterLight[After]})))), type text } ,null,MissingField.Ignore),

    #"ReplaceViaTableColumnC" = Table.TransformColumns(#"ReplaceViaTableColumnB",{ "ColumnC", each Text.Combine(List.ReplaceMatchingItems({_},List.Buffer(List.Zip({tblBeforeAfterLight[Before],tblBeforeAfterLight[After]})))), type text } ,null,MissingField.Ignore),

    #"ReplaceViaTableColumnD" = Table.TransformColumns(#"ReplaceViaTableColumnC",{ "ColumnD", each Text.Combine(List.ReplaceMatchingItems({_},List.Buffer(List.Zip({tblBeforeAfterLight[Before],tblBeforeAfterLight[After]})))), type text } ,null,MissingField.Ignore),

in

    #"ReplaceViaTableColumnD"

 

// (3):  Replace via table on multiple Columns at once (My wish)

let

    Source = Csv.Document(File.Contents("C:\NC20220410.csv"),[Delimiter=";", Columns=45, Encoding=65001, QuoteStyle=QuoteStyle.None]),

    #"En-têtes promus" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),

    #"ReplaceAllColumns" = Table.TransformColumns(#"En-têtes promus",{ {"ColumnA","ColumnB","ColumnC","ColumnD"}, each Text.Combine(List.ReplaceMatchingItems({_},List.Buffer(List.Zip({tblBeforeAfterLight[Before],tblBeforeAfterLight[After]})))), type text } ,null,MissingField.Ignore)

in

    #"ReplaceAllColumns"

 

tblBeforeAfterLight

Before

After

Yes

Oui

No

Non

Maybe

Peut-être

Of Course

Certainement

Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    Your table approach for replacement word is the way to go.

    In the below code, I am replacing it in all the columns of the table through

    ColumnList = Table.ColumnNames(Source)

    But if you need to do in selected columns, then just specify the column names in this ColumList. (You can once again make a table which you can convert to list)

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxKVtJRikwtBpJA5JtYmZQKpFPSUoAkmPDLB0nF6kQrpQEZRWBZNOyXn5cKNQWkDqwGrA0kWQTE/mkKzvmlRcWpYBPA8kCyCGIeSAvEATDbwXqR9UC0QRhKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t]),
    ColumnList = Table.ColumnNames(Source),
    ChangeValues = Table.FromRecords(Table.TransformRows(Source, (r) => List.Accumulate(Table.ColumnNames(Source), r, (s,c)=> Record.TransformFields(s,{{c, each try tblBeforeAfterLight {[Before=_]}[After] otherwise _}})))
        , Value.Type(Source))
in
    ChangeValues

     

3 Replies

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    4 years ago

    Your table approach for replacement word is the way to go.

    In the below code, I am replacing it in all the columns of the table through

    ColumnList = Table.ColumnNames(Source)

    But if you need to do in selected columns, then just specify the column names in this ColumList. (You can once again make a table which you can convert to list)

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxKVtJRikwtBpJA5JtYmZQKpFPSUoAkmPDLB0nF6kQrpQEZRWBZNOyXn5cKNQWkDqwGrA0kWQTE/mkKzvmlRcWpYBPA8kCyCGIeSAvEATDbwXqR9UC0QRhKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t]),
    ColumnList = Table.ColumnNames(Source),
    ChangeValues = Table.FromRecords(Table.TransformRows(Source, (r) => List.Accumulate(Table.ColumnNames(Source), r, (s,c)=> Record.TransformFields(s,{{c, each try tblBeforeAfterLight {[Before=_]}[After] otherwise _}})))
        , Value.Type(Source))
in
    ChangeValues

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    1.- First transpose your table of names and promote headers so you only have one row.

    let
    Source = Excel.CurrentWorkbook(){[Name="Table_Names"]}[Content],
    #"Transposed Table" = Table.Transpose(Source),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true])
    in
    #"Promoted Headers"

     

     

    2.- then add this M code in the formula bar 
    Table.TransformColumnNames( Source, each try Table.Column(Table_Names,_){0} otherwise _)

    let
    Source = Excel.CurrentWorkbook(){[Name="Table_Data"]}[Content],
    Custom1 = Table.TransformColumnNames( Source, each try Table.Column(Table_Names,_){0} otherwise _)
    in
    Custom1