Forum Discussion

DanFromMontreal's avatar
DanFromMontreal
Icon for Helper IV rankHelper 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....
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

     

Vijay_A_Verma's avatar
Vijay_A_Verma
Icon for Most Valuable Professional rankMost 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