Combine Text from one column in Table.TransformColumns

[rpiboy_1]

Let us say we have a table like such:

Group Code	Sub group Code
10	null
10	10
10	20
20	null
20	10
20	20
20	30

In the table I want to transform the column [Sub group Code] in place, so that I get the following result:

Group Code	Sub group Code
10	null
10	10_10
10	10_20
20	null
20	20_10
20	20_20
20	20_30

I'm trying to use:

= Table.TransformColumns(#"PreviousStep", {{"Sub group Code", each Record.Field(_, "Group Code") & "_" & Text.From(_, "en-US"), type text}})

But it throws an error "We cannot apply field access to the type Text". I always seem to struggle with being in the right context for '_' so I'm quite certain my issue is around getting the value from the column 'Group Code'. Suggestions appreciated, explanation always helpful!

I realize I'll need some conditional logic for retaining the null state, but I don't think that is where my issues is?

[ImkeF · Accepted solution]

Hi @rpiboy_1 ,
you have to use a different function for it:
Table.TransformColumns - alternative in PowerBI and PowerQuery in Excel (thebiccountant.com)

[rpiboy_1]

@ImkeF that worked quite nicely! Thank you. Curious to understand more about your performance comment? Is the solution less performant because its using a table function, that has to iterate over the whole table, versus adding and removing columns, which is less intensive?

[rpiboy_1]

@ImkeF thanks for the link/tip. Looks promising! I'll give it a go tomorrow and confirm.