Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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!
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
60 | |
54 | |
27 | |
16 | |
9 |