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
Supercat
Frequent Visitor

Replace Value in several columns

Hello everyone,

     I have been considering this question for a long time. I have a table with several columns where value(integer actually) stores. Also I have another table with column name, integer and the meaning standing for. I have to combine these two tables or actually replace the value from integer to its real meaning. Can anyone help me with this?

 

Supercat_0-1705047341280.png

Supercat_1-1705047433834.png

 

1 ACCEPTED SOLUTION

as I wrote above - be careful. If your data has errors (you either don't have such columns or some values in tbl_A are not listed in tbl_B). We can fix that:

#problem with column names

 

List.Intersect({Table.ColumnNames(tbl_A) , List.Distinct(tbl_B[Column1])})

 

#problem with missing values (nothing to replace with):

 

b = try List.Select(a, (x) => x{0} = value){0}{1} otherwise null][b],

 

 see if it works.

View solution in original post

6 REPLIES 6
slorin
Super User
Super User

Hi,

Another solution with List.ReplaceMatchingItems

 

let
Transform = List.Transform(
Table.ColumnNames(tbl_A),
(x)=> let Selection = Table.SelectRows(tbl_B, each [Column1]=x)
in List.Zip({Selection[selectvalue], Selection[selectname]})),
ReplaceMatchingItems = Table.FromColumns(
List.Transform(
List.Zip({Table.ToColumns(tbl_A), Transform}),
each List.ReplaceMatchingItems(_{0}, _{1})),
Table.ColumnNames(tbl_A))
in
ReplaceMatchingItems

 Stéphane

AlienSx
Super User
Super User

@Supercat sure. List.Distinct(tbl_B[Column1])

Thank you very much. It may goes wrong when 'null' in the transferred column in table_A, the message as following:

Supercat_0-1705066569050.png

 

as I wrote above - be careful. If your data has errors (you either don't have such columns or some values in tbl_A are not listed in tbl_B). We can fix that:

#problem with column names

 

List.Intersect({Table.ColumnNames(tbl_A) , List.Distinct(tbl_B[Column1])})

 

#problem with missing values (nothing to replace with):

 

b = try List.Select(a, (x) => x{0} = value){0}{1} otherwise null][b],

 

 see if it works.

AlienSx
Super User
Super User

hello, @Supercat i did not catch possible errors so be careful

let
    tbl_A = your_a_table, 
    tbl_B = your_another_table,
    columns = {"Unit", "CapexCount"},
    g = Table.Group(tbl_B, "Column1", {"repl", each List.Zip({[selectvalue], [selectname]})}),
    rec = Record.FromList(g[repl], g[Column1]),
    f = (value, col_name) =>
        [a = Record.FieldOrDefault(rec, col_name, {}),
        b = List.Select(a, (x) => x{0} = value){0}{1}][b],
    tx = List.Transform(columns, each {_, (x) => f(x, _)}),
    z = Table.TransformColumns(tbl_A, tx)
in
    z

 

Yes, it worked. But if I need to transfer all the column in table_A, I need to type all the column into the code. Can I just take all the item in table_B 'column 1' to replace the following row? Thank you very much.

columns = {"Unit", "CapexCount"},

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