Forum Discussion

Frequent Visitor

5 years ago

Solved

Split multiple row to multiple row by line break based on character conditions

Hello everyone, I have a dataframe that looks as follows: I want to split multiple row to multiple row by line break and first character each line. Here is the result I want: ...

Need Help

edhans

5 years ago

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.

edhans

Community Champion

5 years ago

See this code piorsenpai

let
    Source = Excel.Workbook(File.Contents("C:\Users\Ed Hansberry\OneDrive\Downloads\test.xlsx"), null, true),
    Data_Sheet = Source{[Item="Data",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Data_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)")
                        ),
                        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)")
                        ),
                        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"

It turns this after I filtered out some blank/empty rows in the Excel file:

into this:

You will need to change the "Source" line to be your folder path to the test.xlsx file.

piorsenpai

Frequent Visitor

5 years ago

It worked on the test.xlsx file. However, when I start working on real data some rows still can work but some is not and occur an error

DataFormat.Error: There were more columns in the result than expected.
Details:
Count=1

edhans

Community Champion

5 years ago

I would have to see the data. There is nothing in my code that is expecting a specific number of columns. Change the "Second Column" code in the advanced editor this and show me what is in one of the lists that is currently returning an error. Row 10 for example, and we can work from there.

    #"Added Second Column" = 
        Table.AddColumn(
            #"Added First Column", 
            "Second Column",
            each 
                let
                    varGroupText = Text.Start([checklist], 1)
                in
                Table.SelectRows(
                    Table.FromList(
                        Text.Split([All Rows][expected]{0}, "#(lf)")
                    ),
                    each Text.Start([Column1],1) = varGroupText
                )[Column1]
        ),

piorsenpai

Frequent Visitor

5 years ago

It still return error value in List

And here is the data in that cell for your information :

edhans

Community Champion

5 years ago

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.

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.

Forum Discussion

Split multiple row to multiple row by line break based on character conditions

Related content

Multiple conditions Index

Multiple Donut charts with conditions

Conditional Column with Multiple Conditions

IF - Multiple conditions

Conditional Column with Multiple Conditions