Forum Discussion

jdbuchanan71's avatar
jdbuchanan71
Super User
7 years ago
Solved

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 ...
Need Help
  • OwenAuger's avatar
    OwenAuger
    7 years ago

    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's avatar
OwenAuger
Super User
7 years ago

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