Forum Discussion
create rows from a column
Let's say I have this:
1, Bob;Mike;Paul, 2, 3
I need to split them into multiple rows
1, Bob, 2, 3
1, Mike, 2, 3
1, Paul, 2, 3
I've tried creating a custom column with something like
=Table.AddColumn(Source,"Custom", Table.FromColumns({Text.Split([Column2], ";")})) but doesn't seem to work :(
Can anyone help this newbi?
Hi,
Here are the steps I used in Edit Query, although there is probably a quicker way this produces the resutls you have requested.
let Source = Excel.Workbook(File.Contents(FilePath &"SplitName.xlsx"), null, true), Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"value1", Int64.Type}, {"value2Names", type text}, {"value3", Int64.Type}, {"value4", Int64.Type}}), #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type", {{"value3", type text}, {"value4", type text}}, "en-AU"),{"value3", "value4"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Merged"), #"Split Column by Delimiter" = Table.SplitColumn(#"Merged Columns", "value2Names", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"value2Names.1", "value2Names.2", "value2Names.3"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"value2Names.1", type text}, {"value2Names.2", type text}, {"value2Names.3", type text}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"value2Names.1", "value2Names.2", "value2Names.3", "Merged"}, "Attribute", "Value"), #"Unpivoted Columns1" = Table.UnpivotOtherColumns(#"Unpivoted Columns", {"Merged", "Attribute", "Value"}, "Attribute.1", "Value.1"), #"Split Column by Delimiter1" = Table.SplitColumn(#"Unpivoted Columns1", "Merged", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Merged.1", "Merged.2"}), #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter1",{"Attribute", "Attribute.1"}) in #"Removed Columns"Excel File Sample: Excel SpltName
File to dowonload DOWNLOAD
Replace parameter FilePath with the location you save the excel file to on your desktop.
Result:
This should work...
2 Replies
Hi,
Here are the steps I used in Edit Query, although there is probably a quicker way this produces the resutls you have requested.
let Source = Excel.Workbook(File.Contents(FilePath &"SplitName.xlsx"), null, true), Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"value1", Int64.Type}, {"value2Names", type text}, {"value3", Int64.Type}, {"value4", Int64.Type}}), #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type", {{"value3", type text}, {"value4", type text}}, "en-AU"),{"value3", "value4"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Merged"), #"Split Column by Delimiter" = Table.SplitColumn(#"Merged Columns", "value2Names", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"value2Names.1", "value2Names.2", "value2Names.3"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"value2Names.1", type text}, {"value2Names.2", type text}, {"value2Names.3", type text}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"value2Names.1", "value2Names.2", "value2Names.3", "Merged"}, "Attribute", "Value"), #"Unpivoted Columns1" = Table.UnpivotOtherColumns(#"Unpivoted Columns", {"Merged", "Attribute", "Value"}, "Attribute.1", "Value.1"), #"Split Column by Delimiter1" = Table.SplitColumn(#"Unpivoted Columns1", "Merged", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Merged.1", "Merged.2"}), #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter1",{"Attribute", "Attribute.1"}) in #"Removed Columns"Excel File Sample: Excel SpltName
File to dowonload DOWNLOAD
Replace parameter FilePath with the location you save the excel file to on your desktop.
Result:
This should work...
Hi,
Try this M code
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Data", type text}}),
#"Replaced Value1" = Table.ReplaceValue(#"Changed Type","1,","1^",Replacer.ReplaceText,{"Data"}),
#"Replaced Value" = Table.ReplaceValue(#"Replaced Value1",";",";1^",Replacer.ReplaceText,{"Data"}),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Replaced Value", {{"Data", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Data"),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Data", type text}}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "Data", Splitter.SplitTextByEachDelimiter({","}, QuoteStyle.Csv, false), {"Data.1", "Data.2"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Data.1", type text}, {"Data.2", type text}}),
#"Trimmed Text" = Table.TransformColumns(#"Changed Type2",{{"Data.2", Text.Trim}}),
#"Filled Up" = Table.FillUp(#"Trimmed Text",{"Data.2"}),
#"Replaced Value2" = Table.ReplaceValue(#"Filled Up","^",",",Replacer.ReplaceText,{"Data.1"}),
#"Merged Columns" = Table.CombineColumns(#"Replaced Value2",{"Data.1", "Data.2"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged")
in
#"Merged Columns"
