Reformat Month Number (where January = 1) to Text Format Returning "01" instead of "1"

maibacherstr — Wed, 26 Jan 2022 15:27:45 GMT

Hello Community, I'm comfortable in Power Query and DAX, but am returning errors when trying to convert month number to a text format while applying IF or SWITCH comparisons and adding a transformation after that. ("DAX comparison operations do not support comparing values of type Text with values of type Integer"). My error formula starts like: IF(MonthNumber < 10, then concatenate "0" and MonthNumber. This cannot be done even while I'm tangling with helper columns).

The real problem to solve is in the subject line. To expand on the problem: I have a few date columns including Month Number (numerical format) which I need to convert to an alphabetized text column. The issue seems simple, which is that months January through September are single digit, meaning I need to plug in a zero before the single digit. Thus, the column will alphabetize from 01 to 12. Without this, the alphabetized column goes: 10, 11, 12, 1, 2, 3... (incorrect!).

I'm happy to pursue solutions in Power Query and/or DAX. Can anyone see through this problem?

MANY THANKS

Re: Reformat Month Number (where January = 1) to Text Format Returning "01" instead of "1"

Greg_Deckler — Wed, 26 Jan 2022 15:33:04 GMT

maibacherstr Try:

Column = VAR __MonthNumberText = MONTH([Date]) & "" VAR __Length = LEN(__MonthNumberText) RETURN IF(__Length = 1, "0" & __MonthNumberText,__MonthNumberText)

Re: Reformat Month Number (where January = 1) to Text Format Returning "01" instead of "1"

maibacherstr — Wed, 26 Jan 2022 15:36:51 GMT

Sir that was amazing! You also gained a Twitter follower. Solution accepted, have a great day.

topic Re: Reformat Month Number (where January = 1) to Text Format Returning "01" instead of "1" in DAX Commands and Tips

Reformat Month Number (where January = 1) to Text Format Returning "01" instead of "1"

Re: Reformat Month Number (where January = 1) to Text Format Returning "01" instead of "1"

Re: Reformat Month Number (where January = 1) to Text Format Returning "01" instead of "1"