Forum Discussion
change number format in query editor
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
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
5 Replies
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,Pattry 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 🙂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
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"
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
