http://datascopic.net/leading-zeros)

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

/in /by

(Download the workbook here:

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 month of March to be: 03
Excel will turn it into: 3

Or, account numbers that are always 6 digits,
Excel will turn 002251
into 2251

This video shows how to force native Excel to recognize leading zeros. Also, the M-Code use of

Text.PadStart

to force Get & Transform to honor leading zeros.

You also see the insertion of steps in a Get and Transform Query, problems with text versus number formatting. Oh! And a few pictures from a recent trip to New York City.

Please, enjoy. This is a 7.5-minute adventure.

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