Forum Discussion
Transform Delimited Data
Hi,
I hae a set of data that looks like below;
| FirstName | Surname | EmployeeID | Result |
| Ryan|Alan|Ryan | Arrowsmith|Delaney|Delaney | 123456|456789|456133 | Positive|Positive|Negative |
I would like to convert it to this;
| FirstName | Surname | EmployeeID | Result |
| Ryan | Arrowsmith | 123456 | Positive |
| Alan | Delaney | 456789 | Positive |
| Ryan | Delaney | 456133 | Negative |
Thankyou
Hi Anonymous, try this code:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCqpMzKtxzAESYJZXalqako6SY1FRfnlxbmZJRo1LKlAytRJOB4NEgUoMjYxNTM1qgNjcwhJEGRob15iCAVA2IL84sySzLLUGzvBLTU+EMPJLFIJLi1KVYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [FirstName = _t, Surname = _t, EmployeeID = _t, Result = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"FirstName", type text}, {"Surname", type text}, {"EmployeeID", type text}, {"Result", type text}}), FirstName = List.Transform(Table.Column(#"Changed Type","FirstName"), Splitter.SplitTextByDelimiter("|")){0}, Surname = List.Transform(Table.Column(#"Changed Type","Surname"), Splitter.SplitTextByDelimiter("|")){0}, EmployeeID = List.Transform(Table.Column(#"Changed Type","EmployeeID"), Splitter.SplitTextByDelimiter("|")){0}, Result = List.Transform(Table.Column(#"Changed Type","Result"), Splitter.SplitTextByDelimiter("|")){0}, #"Combined Lists" = List.Zip({FirstName,Surname,EmployeeID,Result}), #"Converted to Table" = Table.FromList(#"Combined Lists", Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Extracted Values" = Table.TransformColumns(#"Converted to Table", {"Column1", each Text.Combine(List.Transform(_, Text.From), "|"), type text}), #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Column1", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"FirstName", "Surname", "EmployeeID", "Result"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"FirstName", type text}, {"Surname", type text}, {"EmployeeID", Int64.Type}, {"Result", type text}}) in #"Changed Type1"As you can see, this works 4 items, and should work with an indefinite number.
Becomes...
You just have to rename your columns to whatever you want them to be. (EDIT: I changed to the code to rename them back to the original names in the split step. Didn't bother uploading a revised image though)
16 Replies
Hi,
Chris has solved a similar problem here. Hope Anonymous can help you.
Anonymous
Hi, try this:
1. Unpivot columns
2.Split by delimiter
3.Transpose
4. Promote headers
Regards
Victor
Hi,
Taking a hint from Vvelarde solution, try this M code
let Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"FirstName", type text}, {"Surname", type text}, {"EmployeeID", type text}, {"Result", type text}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"), #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Value", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Value.1", "Value.2", "Value.3"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Value.1", type text}, {"Value.2", type text}, {"Value.3", type text}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Index"}), #"Transposed Table" = Table.Transpose(#"Removed Columns"), #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]), #"Changed Type2" = Table.TransformColumnTypes(#"Promoted Headers",{{"FirstName", type text}, {"Surname", type text}, {"EmployeeID", Int64.Type}, {"Result", type text}}) in #"Changed Type2"This will work for as many columns as you keep adding.
Hope this helps.
Hi Anonymous, try this code:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCqpMzKtxzAESYJZXalqako6SY1FRfnlxbmZJRo1LKlAytRJOB4NEgUoMjYxNTM1qgNjcwhJEGRob15iCAVA2IL84sySzLLUGzvBLTU+EMPJLFIJLi1KVYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [FirstName = _t, Surname = _t, EmployeeID = _t, Result = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"FirstName", type text}, {"Surname", type text}, {"EmployeeID", type text}, {"Result", type text}}), FirstName = List.Transform(Table.Column(#"Changed Type","FirstName"), Splitter.SplitTextByDelimiter("|")){0}, Surname = List.Transform(Table.Column(#"Changed Type","Surname"), Splitter.SplitTextByDelimiter("|")){0}, EmployeeID = List.Transform(Table.Column(#"Changed Type","EmployeeID"), Splitter.SplitTextByDelimiter("|")){0}, Result = List.Transform(Table.Column(#"Changed Type","Result"), Splitter.SplitTextByDelimiter("|")){0}, #"Combined Lists" = List.Zip({FirstName,Surname,EmployeeID,Result}), #"Converted to Table" = Table.FromList(#"Combined Lists", Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Extracted Values" = Table.TransformColumns(#"Converted to Table", {"Column1", each Text.Combine(List.Transform(_, Text.From), "|"), type text}), #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Column1", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"FirstName", "Surname", "EmployeeID", "Result"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"FirstName", type text}, {"Surname", type text}, {"EmployeeID", Int64.Type}, {"Result", type text}}) in #"Changed Type1"As you can see, this works 4 items, and should work with an indefinite number.
Becomes...
You just have to rename your columns to whatever you want them to be. (EDIT: I changed to the code to rename them back to the original names in the split step. Didn't bother uploading a revised image though)
