Help with Table.TransformColumns and Text.Insert

[jdbuchanan71]

Good afternoon,

Hoping I am missing a simple solution because of my lack of M knowledge.

I have a table with client, order and invoice and I would like to insert the client_id + "|" in front of both the order_number and invoice_number.  I know I could do it by inserting custom columns and building the string but I was wondering if there is a way to do it using Table.TransformColumns and Text.Insert.  

Something like 

= Table.TransformColumns(Source,{{Text.Insert([Order_Number],0,[Client_Number]&"|")},{Text.Insert([Invoice_Number],0,[Client_Number]&"|")}})

To go from this

Client_ID	Order_Number	Invoice_Number
A	001	100
A	001	200
A	003	300
A	004	400
A	005	500
B	001	100
B	001	200
B	003	300
B	004	400
B	005	500

To this

Client_ID	Order_Number	Invoice_Number
A	A|001	A|100
A	A|001	A|200
A	A|003	A|300
A	A|004	A|400
A	A|005	A|500
B	B|001	B|100
B	B|001	B|200
B	B|003	B|300
B	B|004	B|400
B	B|005	B|500

in one step.

Thank you

[jdbuchanan71]

@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"

[OwenAuger · Accepted solution]

You're welcome 🙂
I think my original reply was marked as spam somehow.

Restating the key points:

Unfortunately Table.TransformColumns can't refer to any columns apart from the column being transformed.

A post on THE BICCOUNTANT blog (credit to @ImkeF) shows how Table.ReplaceValue can be used as a workaround.

The two suggestions I have are:

Table.ReplaceValue

One step required for each column to be 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.TransformRows

The code is a bit more cumbersome in my view, as the table has to be converted to a list of records then back to a table again.

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"

Best regards,

Owen

[OwenAuger]

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