OMG a magic formula – real facepalm moment

Ok, so we’ve all been there. We’ve all needed to pad numbers especially for dates, months are the worst, they kill when you have a mix of one and two digit numbers. While building my latest date dimension I had to combine year and week number to form a column, I’ll not bore you with everything but adding a “0” to pad out those one digits had to be done to make it all work. I looked and I looked and then BOOOM! I found it!

Then today I needed it again for something else, so my advice is write it down, store it keep it close to you, it’s going to be one of those things you use loads during key times then you’ll not touch it for months, days or hours?

Source: http://www.excelguru.ca/blog/2014/08/20/5-very-useful-text-formulas-power-query-edition/


Text.PadStart(Text.From(Date.Month([Date])),2,”0″)


Remember that unlike the rest of Excel Power Query is case sensite to note the capitals.

Date.Month([Date]) = For the value in [Date] column extract the Month number
Text.From(…) = Convert number into text – really this standardises the output
Text.PadStart (…,Length,Padcharacter) = Set a required length and then what to pad “short” values with for the purposes of this 2 character and “0” (Zero)

 

Leave a Reply

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