Change the format of Month in direct query

[game1]

Hello, 

I have date from table1[date] in this format 01/02/2023.

So I want to change my format to have my month like this : 1 for January, 2 for Febuary, .......

I have done this (MonthName), but it return me in the wrong order : April, August, .......

but me, I want January", February, .....

Note: FORMAT cannot be use in direct query. 

MonthName =
SWITCH(
MONTH(table1[date]),
1, "January",
2, "February",
3, "March",
4, "April",
5, "May",
6, "June",
7, "July",
8, "August",
9, "September",
10, "October",
11, "November",
12, "December",
BLANK()
)

[v-kongfanf-msft · Accepted solution]

Hi @game1 ,

As you said,format function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.

I have the following suggestions:
1. because direct query has a lot of limitations for dataset manipulation, especially the use of many dax functions. You can convert direct query to import mode.
2. directly connect the processed date format to powerbi .
3. achieve a similar effect by means of measure.

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Rabi]

Hi Mate,

Do something like this

1) Add a month column as below:

Month = month('Date'[Date])

2) Add Month Name Column as  below

Month Name = FORMAT('Date'[Date].[Month], "mmmm"

When you add the month name in the visual, click on the month name, go to the column tols option, click on sort by, then click on the month to get it sorted in correct order. Follow the steps below:

I hope it helps,

Thanks !!

[game1]

Yes! But that only work in Import option.

With query direct, it is not working, because I can't use FORMAT()