Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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!
Solved! Go to Solution.
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!