To update length of Month Numbers

[Sushant__08]

Dear All,

Currently, month numbers are listed as M1, M2, ~~ , M10, M11 ~~, M100, M101, etc in an excel imported to Power BI.

I want to add a new column in Power BI assigning month numbers as M001, M002, ~~, M010, M011, ~~ , M100, M101, etc.

This is to align month number in X-axis in an ascending order.

I tried roughly in excel and following formula seemed to work:

=IF(LEN(A3)=2,LEFT(A3,1)&"00"&RIGHT(A3,1),IF(LEN(A3)=3,LEFT(A3,1)&"0"&RIGHT(A3,2)))

Can anyone help me to prepare a similar formula in Power BI to add a new month number column ?

Thanks in advance.

[mickey64]

For your reference.

[Sushant__08]

Thank you sir for a reply. I think it would have worked if there was only numerical value. But the month number column has an alphabet (M) as well.

Like, M1 --- > M001 etc.

Could there be any other option to prepare an updated column ?

Regards,

[mickey64 · Accepted solution]

How about these formula？