Splitting Multiple Columns in Power Query | Battling null values and line-feeds

In a previous video I dealt with a common need to split multiple columns of data. However, in that dataset the delimiter was obvious and all cells were filled in.

But! What happens when things get weird? In this video, the delimiter is a line-feed and there’s a problem with empty cells. We use Text.Split and Table.FromColumns in Excel’s Power Query; then we have to go back and get rid of null values.

Download the workbook: https://datascopic.net/SMR2

#PowerQuery
#Text.Split
#SplitMultipleColumns

For a list of my Excel courses at Lynda/LinkedIn:
https://www.linkedin.com/learning/instructors/oz-du-soleil
There are courses on Power Query, Good spreadsheet habits, and a weekly Excel challenge that comes out every Friday.

Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

My old blog: http://datascopic.net/blog-2-2