Entries by

Let’s Celebrate Clean Data and 1000 Subscribers!

Thanks to everyone who’s subscribed, commented, sent in questions that became videos. Around this time last year I changed my video production style and you’ve responded surprisingly well. This channel is about to cross the 1000-subscriber mark. What should we do about that? I would like your suggestions for a video that I can make […]

Excel’s Get and Transform: Handling Leading Zeros and a Little M-Code

(Download the workbook here: http://datascopic.net/leading-zeros) Here’s another video with warning about some things that native Excel handles easier than Get & Transform (Power Query) does, but if we have to do this in Get & Transform, here’s how. Challenge: handling leading zeros. Excel is known for clipping off leading zeroes. If you need a the […]

Unstack Data that is Separated by Bold Text: Challenge w/Kevin Lehrbass – Formulas, VBA, Power Query

Problem: a tall column of data that needs to be unstacked and converted into neat columns and rows. Twist: the distinction where the list needs to be divided is by bold font. (Download the workbook: http://datascopic.net/unstackingboldfont ) Parents’ names are bold. Childrens’ names are regular font. How does this list get unstacked with the children […]

3 Warnings about Excel’s Get and Transform Feature

Excel’s Get & Transform is a fantastic tool, but there are some warnings that you should be aware of before you get stung. (Watch out for the scorpion) This video shows 3 ways you can get stung: 3:20 – 6:24 Column headers are hard-coded. If you need a column header to be variable, it’s a […]

Adding Line-Breaks in an Excel Cell: Using CHAR and SUBSTITUTE

In the previous video I showed how to retrieve Word data and bring it into Excel. However, Nisha in Chicago noticed that the original data was in lists, but I didn’t retain the lists when the data was brought into Excel. (The original video: https://www.youtube.com/watch?v=-jihz5E0X5k) Nisha asked, what it would take to get the data […]

Transferring Data from a Word Table and into Excel

Data is in tables in Microsoft Word and needs to be moved over to Excel–and retain the table structure. PROBLEM: copy from Word and into Excel creates a mess. Items that were in the same cell in Word are in different cells in Excel. The solution is to replace ^p in the document with something […]