Forum Discussion
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
Sourc...
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
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
Seriously awesome!
Thank you so much!
