Change date format with DirectQuery

[Syndicate_Admin]

Hello

I have a database connected via DirectQuery to my panel, in which there is a graph in which, starting from a date type with dd/mm/y format I have gone to a yyamm format and it works well.

Now I want the X axis (the date axis) to appear a date with a user-friendly format (type (mmm yy). I've tried using the Date function in DAX but it doesn't allow it in DirectQuery.

Any ideas on how to do it?

Thank you so much.

[v-eqin-msft · Accepted solution]

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.

[parry2k]

@Syndicate_Admin maybe add another duplicate date column, change the format to what you want, and use that on the x-axis.

✨ Follow us on LinkedIn and  to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

[Syndicate_Admin]

I have added the new column and I have put another format but how do I enter it on the x axis without them coming out in days?

Excuse my inexperience.

Greetings