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.
The steps involved are:
- Load the data to Power Query
- Make two branches of the query loaded
- Branch Query 1 is for split column A into rows
- Add an index column as row identifier
- Branch Query 2 is for splitting column B into rows
- Add an index column as row identifier
- Merge the two queries using the index column as mapping column; and Left Outer join
- Expand the result and get the values
- Remove the index column
- Load the result to worksheet
- Relax and Refresh (when data changes/updated)
As promised, here’s a video to demonstrate the above steps.
Video in English:
Video in Cantonese:
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. 🙂
wmfexcel 
Hello,
I enjoy wmfexcel.
Still accepting comments?
if Item has a number of lines different from Value, like
A2=AAAA BBB CCC , B2=111 222, A3=DDDD FFF, B3=333 444 555 then
what should the result be like?
Should the result table have five rows? Or six rows?
LikeLike
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
LikeLike
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
🍻
LikeLike
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},
in
TblFrmCols
My regards
LikeLiked by 1 person
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
LikeLike