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.
Hello everyone
I have a situation where I want to delimit the following data into different rows:
From:
Index,Data
1,A-B-C-D
2,A-B
To:
Index,Data
1,A-B
1,B-C
1,C-D
2,A-B
I tried following the advice in this post but am unable to adapt it to my case, since there is data that is repeated in each row.
Thank you!
Solved! Go to Solution.
@Anonymous -
This should get it done.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXLUddJ11nVRitWJVjKC8JViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Index = _t, Data = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"Data", type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Data", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Data.1", "Data.2", "Data.3", "Data.4"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Data.1", type text}, {"Data.2", type text}, {"Data.3", type text}, {"Data.4", type text}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Index"}, "Attribute", "Value"),
#"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"Index"}, {{"Count", each _, type table [Index=number, Attribute=text, Value=text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "AddIndex", each Table.AddIndexColumn([Count],"Index2",1,1)),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each let tblName = [AddIndex] in Table.AddColumn([AddIndex],"nextVal", each tblName{[Index2]}[Value])),
#"Expanded Custom.1" = Table.ExpandTableColumn(#"Added Custom1", "Custom.1", {"Index", "Attribute", "Value", "Index2", "nextVal"}, {"Custom.1.Index", "Custom.1.Attribute", "Custom.1.Value", "Custom.1.Index2", "Custom.1.nextVal"}),
#"Replaced Errors" = Table.ReplaceErrorValues(#"Expanded Custom.1", {{"Custom.1.nextVal", null}}),
#"Filtered Rows" = Table.SelectRows(#"Replaced Errors", each ([Custom.1.nextVal] <> null)),
#"Merged Columns" = Table.CombineColumns(#"Filtered Rows",{"Custom.1.Value", "Custom.1.nextVal"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Merged"),
#"Removed Other Columns" = Table.SelectColumns(#"Merged Columns",{"Index", "Merged"})
in
#"Removed Other Columns"
Proud to be a Super User!
@Anonymous -
This should get it done.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXLUddJ11nVRitWJVjKC8JViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Index = _t, Data = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"Data", type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Data", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Data.1", "Data.2", "Data.3", "Data.4"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Data.1", type text}, {"Data.2", type text}, {"Data.3", type text}, {"Data.4", type text}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Index"}, "Attribute", "Value"),
#"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"Index"}, {{"Count", each _, type table [Index=number, Attribute=text, Value=text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "AddIndex", each Table.AddIndexColumn([Count],"Index2",1,1)),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each let tblName = [AddIndex] in Table.AddColumn([AddIndex],"nextVal", each tblName{[Index2]}[Value])),
#"Expanded Custom.1" = Table.ExpandTableColumn(#"Added Custom1", "Custom.1", {"Index", "Attribute", "Value", "Index2", "nextVal"}, {"Custom.1.Index", "Custom.1.Attribute", "Custom.1.Value", "Custom.1.Index2", "Custom.1.nextVal"}),
#"Replaced Errors" = Table.ReplaceErrorValues(#"Expanded Custom.1", {{"Custom.1.nextVal", null}}),
#"Filtered Rows" = Table.SelectRows(#"Replaced Errors", each ([Custom.1.nextVal] <> null)),
#"Merged Columns" = Table.CombineColumns(#"Filtered Rows",{"Custom.1.Value", "Custom.1.nextVal"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Merged"),
#"Removed Other Columns" = Table.SelectColumns(#"Merged Columns",{"Index", "Merged"})
in
#"Removed Other Columns"
Proud to be a Super User!
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 |
---|---|
113 | |
97 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |