Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
KDM_HOSS
Advocate I
Advocate I

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

 

KDM_HOSS_0-1647609209152.png

 

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

 

KDM_HOSS_1-1647609321298.png

 

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

 

KDM_HOSS_2-1647609467880.png

 

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!

1 ACCEPTED SOLUTION
Vijay_A_Verma
Super User
Super User

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

 

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

(almost) only with mouse clicks

Vijay_A_Verma
Super User
Super User

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!

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors