Change date format with DirectQuery

Hi Syndicate_Admin ,

I have tried some effective ways like FORMAT() function , "Split" feature ,  M syntax below and so on... None worked when using Direct Query mode. So you may need to switch to Import mode.

Date.MonthName([Date]) &" " 
 & Text.End(Text.From( Date.Year([Date])),2)

Or with DQ mode, please follow these steps that could successfully get the expected format on my side.

In  Power Query

1. Right-click Date column to Duplicate the DateTime column --> Change type to Text

2. Click "Add Column" tab -->Use Text.Start() to add a custom column to get "mmm"

Text.Start([#"Date - Copy"],3)

3.Select Date column-->Click "Add Column" tab -->Date-Year only

4. Add custom column:

=[mmm]&" "& Text.End( Text.From([Year]),2)

5. Remove unnecessary columns,  below is the final table:

The whole M syntax:

let
    Source = Sql.Database("WX-85154\MSSQLSERVER01", "Eyelyn Test"),
    dbo_DateValue = Source{[Schema="dbo",Item="DateValue"]}[Data],
    #"Duplicated Column" = Table.DuplicateColumn(dbo_DateValue, "Date", "Date - Copy"),
    #"Changed Type" = Table.TransformColumnTypes(#"Duplicated Column",{{"Date - Copy", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "mmm", each Text.Start([#"Date - Copy"],3)),
    #"Inserted Year" = Table.AddColumn(#"Added Custom", "Year", each Date.Year([Date]), Int64.Type),
    #"Added Custom1" = Table.AddColumn(#"Inserted Year", "mmm yy", each [mmm]&" "& Text.End( Text.From([Year]),2)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Year", "Date - Copy", "mmm"})
in
    #"Removed Columns"

Now you could use mmm yy column as X-axis:

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