Excel Power Query: Import Data from Web Page and Clear Line-Breaks

Power Query has Clean and Trim features, but sometimes those don’t work with line-breaks.

1:49 Importing the data from the Wikipedia page
2:29 Unpivot
3:13 Split columns
3:35 Explanation of the problem of the data being on multiple lines
4:30 Clean & Trim (they don’t work)
4:48 Setting up the data to
5:36 #(lf) to expose the line-feeds
6:22 Split columns
6:50 Unpivot

This video imports data from a Wikipedia page and cleanses it. The problem is each cell has a tall column of data that needs to be parsed. To do this, watch how we have to get crafty because it’s necessary to handle this with M code.

We have to use #(lf)

Along the way, you’ll see:
Unpivot,
Replace Values
Trim and Cleanse
And lots of fire.

Contact me:
oz1.depot@gmail.com

Website: http://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition
http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336
My old blog: http://datascopic.net/blog-2-2

0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *