Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
4 years ago
Solved

Change date format with DirectQuery

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.

  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

3 Replies

  • parry2k's avatar
    parry2k
    Super User
    4 years ago

    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's avatar
      Syndicate_Admin
      Administrator
      4 years ago

      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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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.