Forum Discussion

Afirmanu's avatar
Afirmanu
Regular Visitor
2 years ago
Solved

change Format YYYYMM to MMM YY

Hello, I have a column containing YYYYMM, where I want to change to MMM YY format in DAX power BI as below

 

Any help will be appreciated

 

General Comment
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Afirmanu ,

    Thank you AlienSx very much for the solution, and I've tried other ways to help you understand the problem:

    For this problem of yours, I think the first step we have to define our data type, defining him as text type will help to help you out.

     

    FormattedDate = 
VAR YearPart = LEFT([Posting Period], 4)
VAR MonthPart = RIGHT([Posting Period], 2)
VAR DateValue = DATE(VALUE(YearPart), VALUE(MonthPart), 1)
RETURN FORMAT(DateValue, "MMM YY")

     

    This is the place where we have to sort the dates before we can realize the visual object you want.

    This is because if you don't sort them, in the matrix, they will be sorted according to the size of the letters.

     

    MonthSortOrder = 
SWITCH(
   LEFT([Posting Period], 6),
   "202401", 1,
   "202402", 2,
   "202403", 3,
   "202404", 4,
   BLANK()
)

     

    Regarding the problem you are having with the super user suggestion,

    I think you are not putting the super user code into the advance editor in the power query, you can try that.

    Hope it helps!

     

    Best regards,
    Community Support Team_ Tom Shen

     

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

     

     

     

     

     

5 Replies

  • AlienSx's avatar
    AlienSx
    Super User
    2 years ago
        columns = Table.ColumnNames(from_this),
    to_this = Table.RenameColumns(
        from_this,
        List.Transform(
            columns, 
            (x) => {x, Date.ToText(Date.FromText(x, [Format = "yyyyMM"]), [Format = "MMM yy"])}
        )
    )
    • Afirmanu's avatar
      Afirmanu
      Regular Visitor
      2 years ago

      Thank you AlienSx  for helping answer

      Is it like this?

      If based on a table like this

       

      Thanks

      • AlienSx's avatar
        AlienSx
        Super User
        2 years ago

        Afirmanu original "from this" is smth different. Sorry, can't help you when you change your data on the fly. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Afirmanu ,

    Thank you AlienSx very much for the solution, and I've tried other ways to help you understand the problem:

    For this problem of yours, I think the first step we have to define our data type, defining him as text type will help to help you out.

     

    FormattedDate = 
VAR YearPart = LEFT([Posting Period], 4)
VAR MonthPart = RIGHT([Posting Period], 2)
VAR DateValue = DATE(VALUE(YearPart), VALUE(MonthPart), 1)
RETURN FORMAT(DateValue, "MMM YY")

     

    This is the place where we have to sort the dates before we can realize the visual object you want.

    This is because if you don't sort them, in the matrix, they will be sorted according to the size of the letters.

     

    MonthSortOrder = 
SWITCH(
   LEFT([Posting Period], 6),
   "202401", 1,
   "202402", 2,
   "202403", 3,
   "202404", 4,
   BLANK()
)

     

    Regarding the problem you are having with the super user suggestion,

    I think you are not putting the super user code into the advance editor in the power query, you can try that.

    Hope it helps!

     

    Best regards,
    Community Support Team_ Tom Shen

     

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

     

     

     

     

     

  • dufoq3's avatar
    dufoq3
    Community Champion
    2 years ago

    Hi Afirmanu, check this:

     

    Before

     

    After

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxU0lEyBGIjIDYGYhOlWJ1opaQkINMUiM2A2ByILcDCyclApiVIhwGIAOkzNFKKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, #"202401" = _t, #"202402" = _t, #"202403" = _t, #"202404" = _t]),
    RanemeColumnsDynamic = Table.TransformColumnNames(Source, each try Date.ToText(Date.From(_ & "01"), [Format="MMM yy", Culture="en-US"]) otherwise _ )
in
    RanemeColumnsDynamic