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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors