Forum Discussion
Split multiple row to multiple row by line break based on character conditions
Here you go.
let Source = Excel.Workbook(File.Contents("C:\Users\Ed Hansberry\OneDrive\Downloads\split_test.xlsx"), null, true), test_Sheet = Source{[Item="test",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(test_Sheet, [PromoteAllScalars=true]), #"Filtered Rows" = Table.SelectRows(#"Promoted Headers", each ([checklist] <> null)), #"Grouped Rows" = Table.Group(#"Filtered Rows", {"num", "checklist"}, {{"All Rows", each _, type table [num=number, checklist=text, sop=text, expected=text]}}), #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Grouped Rows", {{"checklist", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "checklist"), #"Filtered Rows1" = Table.SelectRows(#"Split Column by Delimiter", each ([checklist] <> "")), #"Added First Column" = Table.AddColumn( #"Filtered Rows1", "First Column", each let varGroupText = Text.Start([checklist], 1) in Text.Combine( Table.SelectRows( Table.FromList( Text.Split([All Rows][sop]{0}, "#(lf)"), Splitter.SplitByNothing() ), each Text.Start([Column1],1) = varGroupText )[Column1], "#(lf)" ) ), #"Added Second Column" = Table.AddColumn( #"Added First Column", "Second Column", each let varGroupText = Text.Start([checklist], 1) in Text.Combine( Table.SelectRows( Table.FromList( Text.Split([All Rows][expected]{0}, "#(lf)"), Splitter.SplitByNothing() ), each Text.Start([Column1],1) = varGroupText )[Column1], "#(lf)" ) ), #"Removed Other Columns" = Table.SelectColumns(#"Added Second Column",{"num", "checklist", "First Column", "Second Column"}) in #"Removed Other Columns"Again, change the source to your file. THe key was adding
Splitter.SplitByNothing()to the Table.FromList functions in each column.
Apparently the unusual ASCII characters was causing Power Query to split more than you wanted, so this forces no splitting within a Table.FromList. If ImkeF knows more I'd love her wisdom on this usage.
You need to be 100% sure nothing is being dropped. I cannot read that language so it isn't efficient for me to do character comparisons.
It still return error value in List
And here is the data in that cell for your information :
Here you go.
let
Source = Excel.Workbook(File.Contents("C:\Users\Ed Hansberry\OneDrive\Downloads\split_test.xlsx"), null, true),
test_Sheet = Source{[Item="test",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(test_Sheet, [PromoteAllScalars=true]),
#"Filtered Rows" = Table.SelectRows(#"Promoted Headers", each ([checklist] <> null)),
#"Grouped Rows" = Table.Group(#"Filtered Rows", {"num", "checklist"}, {{"All Rows", each _, type table [num=number, checklist=text, sop=text, expected=text]}}),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Grouped Rows", {{"checklist", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "checklist"),
#"Filtered Rows1" = Table.SelectRows(#"Split Column by Delimiter", each ([checklist] <> "")),
#"Added First Column" =
Table.AddColumn(
#"Filtered Rows1",
"First Column",
each
let
varGroupText = Text.Start([checklist], 1)
in
Text.Combine(
Table.SelectRows(
Table.FromList(
Text.Split([All Rows][sop]{0}, "#(lf)"),
Splitter.SplitByNothing()
),
each Text.Start([Column1],1) = varGroupText
)[Column1],
"#(lf)"
)
),
#"Added Second Column" =
Table.AddColumn(
#"Added First Column",
"Second Column",
each
let
varGroupText = Text.Start([checklist], 1)
in
Text.Combine(
Table.SelectRows(
Table.FromList(
Text.Split([All Rows][expected]{0}, "#(lf)"),
Splitter.SplitByNothing()
),
each Text.Start([Column1],1) = varGroupText
)[Column1],
"#(lf)"
)
),
#"Removed Other Columns" = Table.SelectColumns(#"Added Second Column",{"num", "checklist", "First Column", "Second Column"})
in
#"Removed Other Columns"
Again, change the source to your file. THe key was adding
Splitter.SplitByNothing()to the Table.FromList functions in each column.
Apparently the unusual ASCII characters was causing Power Query to split more than you wanted, so this forces no splitting within a Table.FromList. If ImkeF knows more I'd love her wisdom on this usage.
You need to be 100% sure nothing is being dropped. I cannot read that language so it isn't efficient for me to do character comparisons.
Thank you so much! I can see It worked perfectly now. Very appriciate that!
Glad it helped piorsenpai - I learned something myself today. 😁
Oh, I figured it out. It was the commas in your list piorsenpai.
Table.FromList automatically attempts to split text in lists where there is a comma.
So this simple list will cause the same error:
let Source = {"Test", "Test, more test", "test3"}, ConvertToTable = Table.FromList(Source) in ConvertToTableSame error - more columns than expected:
Add Splitter.SplitByNothing() as the 2nd parameter of Table.ToList fixes it.
let Source = {"Test", "Test, more test", "test3"}, ConvertToTable = Table.FromList(Source, Splitter.SplitByNothing()) in ConvertToTableI still don't see the logic of why Table.FromList attempts to do any parsing by default though, but it does.
This code works - and creates a 2 column table.let Source = {"Test, Another", "Test, more test", "test3, final"}, ConvertToTable = Table.FromList(Source) in ConvertToTable
FYI ImkeF