change number format in query editor

[Anika3186]

Hello,

I have a column ("calendar week) with numbers from 1-53 (the column is formatted as Integer). Now I want to put a 0 in front of the numbers 1-9 (=> 01,02,03, etc).

Can someone help me and tell me how to do it?

Thanks!

Anika

[MarkSmit]

Hi Anika,
What i do is:

1. select column Calander week

2. transform column and add prefix "0"

3. transfrom same column and extract last 2 digits

If you want to keep your original column then use add column.

greatings

[mahoneypat · Accepted solution]

Please try this approach in a DAX column, using your table/column names.

NewWeekNum = FORMAT(WEEKNUM('Date'[Date]), "00")

Or you can do it in the query editor with this formula in a custom column

= Text.PadStart(Text.From(Date.WeekOfYear([DateColumn])), 2, "0")

Regards,

Pat

[StefanoGrimaldi]

add a if statement, new column if(table[weeknumber]>9, table[weeknumber],"0"&CONVERT('Table'[caledar week],STRING)) 

note: this its for the weeknumber format, you make this a separate column and them concatenate with the first part you have before the weeknumber the "1-"weeknumber

also if you doing it in power query M code for the if satetemente instead of , for each part of the statem you use something like this ... if "conditions" them "statement if true" else "statement if false"

[StefanoGrimaldi]

try this: 

new Column = "0"&CONVERT('Table'[caledar week],STRING)

this will convert the number in text and add the 0 before, as that format 01,02,03 will only exist as tex as number the 0 will get deleted in from of it. 

if they resolve your question please mark as solution and if you liked it give some kudos 🙂

[Anika3186]

Many thanks for your response.
When I write this expression, it also add a 0 in front of the numbers 10-53 (010, 011,053 etc.)

I only want to add a 0 in front of the numbers 1-9, so that all numbers in the column are two-digit