Split cell contents separated by line feed into rows with #Excel #PowerQuery – Part 2

Power BI Course.

Last week, I showed you how to split cell contents separated by line feed into rows with Power Query.  That was easy when we are dealing with only one column.  If you don’t know how, you may read the post here before you continue.

When we have two columns, like the screenshot below, it’s getting a bit complicating… but still very easy with Power Query.  It just requires a few more steps.

Split cells with linefeed (end)

The steps involved are:

  1. Load the data to Power Query
  2. Make two branches of the query loaded
  3. Branch Query 1 is for split column A into rows
    • Add an index column as row identifier
  4. Branch Query 2 is for splitting column B into rows
    • Add an index column as row identifier
  5. Merge the two queries using the index column as mapping column; and Left Outer join
  6. Expand the result and get the values
  7. Remove the index column
  8. Load the result to worksheet
  9. Relax and Refresh (when data changes/updated)

As promised, here’s a video to demonstrate the above steps.

If you have already read the previous post and would like to jump to how to deal with two columns, please start watching from 03:33.

As you see, Power Query is such a powerful data cleansing tool that every serious Excel user should know about.

You may download the sample files to following along.

End note:

You may be thinking… what if we have more columns of this kind of “dirty” data.  The above method may work fine when we are dealing with 2 columns, or three columns at most.  When it comes to 5 columns, or even 10 columns, the method described could be really tedious and time consuming.  I totally agree with you.  Nevertheless, I don’t have a solution for that.  If you know or have any suggestions, please share with us by leaving comments. πŸ™‚

Power BI Course.

About MF

An Excel nerd who doesn’t work on Excel most of the time at current job...... πŸ€” Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
This entry was posted in Excel in Action, Power Query and tagged , . Bookmark the permalink.

4 Responses to Split cell contents separated by line feed into rows with #Excel #PowerQuery – Part 2

  1. Oyekunle SOPEJU says:

    Hi Wong,
    A good one no doubt. Worked on something similar recently.
    Four columns needed to be split.
    The heavy-lifting was done with Table.TransformColumns() function.
    All i did was to edit the function in the Advanced Editor, and either of the two codes below did a neat job. reducing the number of steps involved.

    TrsfmCols = Table.TransformColumns(Source , {
    {“Item” , each Lines.FromText( _ )} ,
    {“Value” , each Lines.FromText( _ )}
    })

    OR

    TrsfmCols = Table.TransformColumns(Source,{
    {“Item” , each Text.Split( _ , “#(lf)” ) } ,
    {“Value” , each Text.Split( _ , “#(lf)” ) }
    } )
    You may add many more columns as needed/required for splitting.
    Regards

    Like

    • MF says:

      Hi SOPEJU,
      Thanks for sharing. I tried the code but failed to make it work… the result was two lists, which when I expanded to rows, it eventually doubled the rows…
      Not sure what I did wrong???
      I will need to spend more time to study the function.
      Have a nice weekend
      🍻

      Like

      • Oyekunle SOPEJU says:

        Hello Wong,
        What i shared with you was the heavy-lifting using Table.TransformColumns() function.

        Below is the full PQ code for your sample file.

        let
        Source = Excel.CurrentWorkbook(){[Name=”Table1″]}[Content] ,

        TrsfmCols = Table.TransformColumns(Source,{
        {“Item” , each Text.Split( _ , “#(lf)” ) } ,
        {“Value” , each Text.Split( _ , “#(lf)” ) }
        } ) ,

        TrsfmCols02 = Table.TransformColumns( TrsfmCols , {
        {“Item” , each List.Combine( TrsfmCols[Item] ) } ,
        {“Value” , each List.Combine( TrsfmCols[Value] ) }
        } ) {0},

        Rec2Tbl = Record.ToTable(TrsfmCols02),
        TblFrmCols = Table.FromColumns( Rec2Tbl[Value] , Rec2Tbl[Name] )
        

        in
        TblFrmCols

        My regards

        Liked by 1 person

        • MF says:

          Hi SOPEJU,
          Thanks so much for the full code. Your code works like a charm.
          Glad to learn new functions. There are so much to explore in PQ.
          Cheers,
          MF

          Like

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.