Forum Discussion

Anika3186's avatar
Anika3186
Frequent Visitor
5 years ago
Solved

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...
Need Help
  • mahoneypat's avatar
    mahoneypat
    5 years ago

    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's avatar
StefanoGrimaldi
Icon for Resident Rockstar rankResident Rockstar
5 years ago

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"