For example:
- 0002-800 -> 00002-0800
- 0001-0800 -> 00001-0800
- 12345-220 -> 12345-0220
Any help is appreciated. Thanks
Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
For example:
Any help is appreciated. Thanks
Solved! Go to Solution.
Hi @pbeeyeqs,
Based on Vvelarde's suggestion, I have something to add。 Before adding the custom column, please remove the "Change type" step after spliting column.
Then, you can paste the provided code into "add custom column" box.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjAwMNK1MDBQitUBcwx1DWA8QyNjE1NdIyMgLxYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}}), #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "ID", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"ID.1", "ID.2"}), #"Added Custom" = Table.AddColumn(#"Split Column by Delimiter", "Custom", each Text.Repeat("0",5-Text.Length([ID.1])) & [ID.1] & "-" & Text.Repeat("0",4-Text.Length([ID.2])) & [ID.2]), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"ID.1", "ID.2"}) in #"Removed Columns"
Best regards,
Yuliana Gu
Hi @pbeeyeqs,
Based on Vvelarde's suggestion, I have something to add。 Before adding the custom column, please remove the "Change type" step after spliting column.
Then, you can paste the provided code into "add custom column" box.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjAwMNK1MDBQitUBcwx1DWA8QyNjE1NdIyMgLxYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}}), #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "ID", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"ID.1", "ID.2"}), #"Added Custom" = Table.AddColumn(#"Split Column by Delimiter", "Custom", each Text.Repeat("0",5-Text.Length([ID.1])) & [ID.1] & "-" & Text.Repeat("0",4-Text.Length([ID.2])) & [ID.2]), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"ID.1", "ID.2"}) in #"Removed Columns"
Best regards,
Yuliana Gu
Hi, you can do with Query Editor
Step 1: Select your column and Split it by Delimiter.
Step 2: Add a Custom Column : (ID.1 y ID.2 is the splitted columns).
Text.Repeat("0",5-Text.Length([ID.1])) & [ID.1] & "-" & Text.Repeat("0",4-Text.Length([ID.2])) & [ID.2]
Step 3: Remove other columns (ID1 and ID2)
Regards
Victor
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
98 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |