Help with Table.TransformColumns and Text.Insert

Hi jdbuchanan71

Table.TransformColumns is not able to refer to any values in columns other than the column being transformed, so unfortunately can't quite do what you want.

(see this post)

The post above suggests an alternative using Table.ReplaceValue.

You could also used Table.ReplaceRows.

Here are some examples to illustrate how you might do it:

Table.ReplaceValue

Requires one step for each column that is transformed

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIwMASShgYGSrE6yCJGKCLGQNIYRcQESJqgiJgCSVOoiBOGyU4YJjthmOyEYbITqsmxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Client_ID = _t, Order_Number = _t, Invoice_Number = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Client_ID", type text}, {"Order_Number", type text}, {"Invoice_Number", type text}}),
    #"Replace Order_Number" = Table.ReplaceValue(#"Changed Type", each [Order_Number],each [Client_ID]&"|"&[Order_Number],Replacer.ReplaceText,{"Order_Number"}),
    #"Replace Invoice_Number" = Table.ReplaceValue(#"Replace Order_Number", each [Invoice_Number],each [Client_ID]&"|"&[Invoice_Number],Replacer.ReplaceText,{"Invoice_Number"})
in
    #"Replace Invoice_Number"

Table.ReplaceRows

A bit cumbersome; creates a list of records then turns this back into a table 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIwMASShgYGSrE6yCJGKCLGQNIYRcQESJqgiJgCSVOoiBOGyU4YJjthmOyEYbITqsmxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Client_ID = _t, Order_Number = _t, Invoice_Number = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Client_ID", type text}, {"Order_Number", type text}, {"Invoice_Number", type text}}),
    #"Transform Rows" = Table.FromRecords(Table.TransformRows(#"Changed Type", each [Client_ID=[Client_ID], Order_Number=[Client_ID]&"|"&[Order_Number], Invoice_Number=[Client_ID]&"|"&[Invoice_Number] ] )),
    #"Restore table type" = Value.ReplaceType(#"Transform Rows", Value.Type(#"Changed Type"))
in
    #"Restore table type"

Regards,

Owen

OwenAuger

I don't know where your reply went but I had copied your formula befor it disappeared (thank goodness).  I ended up going with your first suggestions which cuts it down to 1 step per transformation at least.

Thank you for your code and if you put the reply back up I can mark it as the answer.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIwMASShgYGSrE6yCJGKCLGQNIYRcQESJqgiJgCSVOoiBOGyU4YJjthmOyEYbITqsmxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Client_ID = _t, Order_Number = _t, Invoice_Number = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Client_ID", type text}, {"Order_Number", type text}, {"Invoice_Number", type text}}),
    #"Replace Order_Number" = Table.ReplaceValue(#"Changed Type", each [Order_Number],each [Client_ID]&"|"&[Order_Number],Replacer.ReplaceText,{"Order_Number"}),
    #"Replace Invoice_Number" = Table.ReplaceValue(#"Replace Order_Number", each [Invoice_Number],each [Client_ID]&"|"&[Invoice_Number],Replacer.ReplaceText,{"Invoice_Number"})
in
    #"Replace Invoice_Number"