cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Syndicate_Admin
Administrator
Administrator

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

1 ACCEPTED SOLUTION

Accepted Solutions
Syndicate_Admin
Administrator
Administrator

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

View solution in original post

6 REPLIES 6
Syndicate_Admin
Administrator
Administrator

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 🙂

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

Syndicate_Admin
Administrator
Administrator

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"

Syndicate_Admin
Administrator
Administrator

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
Syndicate_Admin
Administrator
Administrator

You probably have your answer by now but I'll post for the record.

Since you asked about the PQ Editor, my answer is specific to it. I had the same problem. I found it easiest in PQ Editor to Add a Conditional Column with the series beginning:

if [column} = 1 then 01

if [column] = 2 then 02

etc

Syndicate_Admin
Administrator
Administrator

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

 

Helpful resources

Announcements
Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Super User 2 - 2022 Congratulations

Welcome Super Users

The Super User program for 2022 - Season 2 has kicked off!

September Events 2022

Check out all of these events

Attend in person or online, there are incredible conferences and events happening all throughout the month of September.

Government Carousel

New forum: GCC, GCCH, DoD - Federal App Makers (FAM)

In response to the unique and evolving requirements of the United States public sector, Microsoft has created Power Apps US Government.

Top Kudoed Authors
Users online (3,808)