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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors