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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
jdbuchanan71
Super User
Super User

Help with Table.TransformColumns and Text.Insert

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_IDOrder_NumberInvoice_Number
A001100
A001200
A003300
A004400
A005500
B001100
B001200
B003300
B004400
B005

500

 

To this

Client_IDOrder_NumberInvoice_Number
AA|001A|100
AA|001A|200
AA|003A|300
AA|004A|400
AA|005A|500
BB|001B|100
BB|001B|200
BB|003B|300
BB|004B|400
BB|005B|500

 

in one step.

 

Thank you

1 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

 

 

 


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

3 REPLIES 3
jdbuchanan71
Super User
Super User

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

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

 

 

 


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn
OwenAuger
Super User
Super User

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

 


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.