Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
Hi @kersplash, 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)
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi @kersplash, 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)
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingGreat @kersplash . Glad your project can move forward.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingI know @ImkeF has tricks for this, also @edhans .
Hi, try this:
1. Unpivot columns
2.Split by delimiter
3.Transpose
4. Promote headers
Regards
Victor
Thanks for this.
Do you know how to allow for variable numbers of delimited components, ie. more fields separated by the the pipe symbol, without having to edit the query?
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.
I only get 3 rows using this code?
Hi,
I do not know whom you are replying to but if it is me then that is what the result should show - only 3 rows.
@Ashish_Mathur Sorry yes your code only gives me 3 rows, if I add more data I still get three rows
Hi,
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}}),
#"Merged Columns" = Table.CombineColumns(#"Changed Type1",{"Value.1", "Value.2", "Value.3"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Merged"),
#"Split Column by Delimiter1" = Table.ExpandListColumn(Table.TransformColumns(#"Merged Columns", {{"Merged", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Merged"),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Merged", type text}}),
Partition = Table.Group(#"Changed Type2", {"Attribute"}, {{"Partition", each Table.AddIndexColumn(_, "Index1",1,1), type table}}),
#"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Index", "Merged", "Index1"}, {"Index", "Merged", "Index1"}),
#"Sorted Rows" = Table.Sort(#"Expanded Partition",{{"Index", Order.Ascending}, {"Index1", Order.Ascending}}),
#"Pivoted Column" = Table.Pivot(#"Sorted Rows", List.Distinct(#"Sorted Rows"[Attribute]), "Attribute", "Merged"),
#"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index", "Index1"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"FirstName", "Surname", "EmployeeID", "Result"})
in
#"Reordered Columns"
Hope this helps.
It works fine for me. Download my Excel file from here. Add data by rows in teh blue range and go to Data > Refresh All.
Hope this helps.
@Ashish_Mathur Sorry there is a misunderstanding on extra data being added.
It is not extra rows of data, it is extra data in the single row that I started with but just more data separated by more pipe symbols.
Example
FirstName | Surname | EmployeeID | Result |
Ryan|Alan|Ryan|Test | Arrowsmith|Delaney|Delaney|Test | 123456|456789|456133|1234 | Positive|Positive|Negative|Positive |
Hi,
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.ExpandListColumn(Table.TransformColumns(#"Unpivoted Other Columns", {{"Value", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Value"),
Partition = Table.Group(#"Split Column by Delimiter", {"Attribute"}, {{"Partition", each Table.AddIndexColumn(_, "Index1",1,1), type table}}),
#"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Index", "Value", "Index1"}, {"Index", "Value", "Index1"}),
#"Sorted Rows" = Table.Sort(#"Expanded Partition",{{"Index", Order.Ascending}, {"Index1", Order.Ascending}}),
#"Pivoted Column" = Table.Pivot(#"Sorted Rows", List.Distinct(#"Sorted Rows"[Attribute]), "Attribute", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index", "Index1"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"FirstName", "Surname", "EmployeeID", "Result"})
in
#"Reordered Columns"
Hi,
Chris has solved a similar problem here. Hope @Anonymous can help you.
User | Count |
---|---|
88 | |
73 | |
69 | |
64 | |
56 |
User | Count |
---|---|
98 | |
92 | |
84 | |
74 | |
66 |