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
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
Solved! Go to 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
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
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.
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
109 | |
108 | |
93 | |
61 |
User | Count |
---|---|
171 | |
139 | |
133 | |
103 | |
86 |