Forum Discussion

piorsenpai's avatar
piorsenpai
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's avatar
    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's avatar
edhans
Community Champion
5 years ago

I cannot tell what you are wanting based on what you have posted piorsenpai - that is just a lot of text that when I paste it into Excel or a text editor, it is one long string. 

 

But if you want to break based on line feeds, you can use the Split Column feature and use special characters.

It turns this:

into this:

If you need more help, I think you are going to have to share an Excel file via OneDrive or Dropbox with real sample data. Pasting text into the forum will destroy any of the special characters you are trying to parse. That includes your expected results. It looks a bit jumbled to me based on what the forum did with it.

 

 

 

piorsenpai's avatar
piorsenpai
Frequent Visitor
5 years ago

Sorry for that inconvenient,

 

Here is excel file which I added Data sample and Result that I want in 2 sheets:

https://1drv.ms/x/s!AgaDgBBM4rAGli_UZM0WlmdiT7wj?e=rG2tqQ

 

If possible, I hope I can have a method for multiple row like that not just 3 rows in file excel sample because the file which I am working on have thousands of row like that.

  • edhans's avatar
    edhans
    Community Champion
    5 years ago

    Ok, see if this works. It turns this:

    into this:

    This is the full code:

    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"

    You will need to change your path in the SOURCE line for it to connect to your test.xlsx file on your hard drive.