Forum Discussion

KDM_HOSS's avatar
KDM_HOSS
Advocate I
4 years ago
Solved

Replace values in a table using a table containing enumeration and column names

I'm attempting to use a table containing index, value, and column name to replace values in another table. 

 

Here's an example of what I'm trying to accomplish.

 

Source Table

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlDSUTIGYgMoDs7PTQ1LzClNNVCK1YlWMoRKg7AhsrQhXNoIKm2ELG0EljZA0mmMLG2sFBsLAA==", 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])
in
    Source

 

 

Enumeration Table

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zcy7DYAwEIPhXa5OwZ1vg+xAE2UDHhX7A4osxVC4+WV9rVk9t2s/3Iotz6qvi/UyZx/Zpxx8h7yD7/i9Y+T4ZIyMKYM2xAZtiA3aEBu0IXbSTrGTdoqdtFPspJ2v3W8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ColumnName = _t, Index = _t, Value = _t])
in
    Source

 

 

Source Table (After Changes)

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjYMM1DSUXI2CjMGUcYQngmYCs7PTQ1LzClNNVCK1QErNURRagxRaois1BBNqRGKUiNkpUYwpagOMIQoNUZWaqwUGwsA", 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])
in
    Source

 

 

I'm dealing with hundreds of source and enumeration tables. The solution I'm looking for would need to work generically and not just on this one example.

 

Preemptive thanks!

Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    Use this in Query1. Enumeration table is referred as Query2 in below m-code

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlDSUTIGYgMoDs7PTQ1LzClNNVCK1YlWMoRKg7AhsrQhXNoIKm2ELG0EljZA0mmMLG2sFBsLAA==", 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]),
    ColumnList = Table.ColumnNames(Source),
    ProcessColumns = Table.FromRecords(
    Table.TransformRows(Source, (r) => List.Accumulate(ColumnList, r, (s,c)=> 
        Record.TransformFields(s,{{c, each try Query2{[ColumnName=c, Index=_]}[Value] otherwise _}})))
        , Value.Type(Source))
in
    ProcessColumns

     

3 Replies

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

    Use this in Query1. Enumeration table is referred as Query2 in below m-code

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlDSUTIGYgMoDs7PTQ1LzClNNVCK1YlWMoRKg7AhsrQhXNoIKm2ELG0EljZA0mmMLG2sFBsLAA==", 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]),
    ColumnList = Table.ColumnNames(Source),
    ProcessColumns = Table.FromRecords(
    Table.TransformRows(Source, (r) => List.Accumulate(ColumnList, r, (s,c)=> 
        Record.TransformFields(s,{{c, each try Query2{[ColumnName=c, Index=_]}[Value] otherwise _}})))
        , Value.Type(Source))
in
    ProcessColumns