Forum Discussion

Spigaw's avatar
Spigaw
Helper III
4 years ago
Solved

Format date directly in the function

Hello everyone,

 

I have a column with periods in the format yyyymm, for example 20209, 202010. I have to output a date from it in the format dd/mm/yyyy, for example 01/09/2020 and 01/10/2020.

 

The function I am currently using is as follows:

 

= Date.From(#date(Number.FromText(Text.Start(Number.ToText([Test]),4)),Number.FromText(Text.End(Number.ToText([Test]),2)),1))

 

However, the output is in numeric format when I load the data in Excel, for example 44075 and 44105.

 

Is there a way to get the right format in the function, without creating a new step in the query?

 

Thanks in advance for any help.

Need Help
  • CNENFRNL's avatar
    CNENFRNL
    4 years ago

     

    = Table.AddColumn(#"Changed Type", "Start of Month", each Date.From(Text.From([yyyymm])&"01"), type date)
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Spigaw ,

     

    Based on your M synatx, It seemed that the data type has changed from text to Number firstly by default ,which is not necessary.

     

    So please delete the Changed type step in order to simplify your code and then use the following synatx in the Advanced Editor:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMrBUitWBsAwNlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Test = _t]),
    #"Added Custom" =Table.TransformColumnTypes( Table.AddColumn(Source, "Custom", each Date.From(#date(Number.FromText(Text.Start([Test],4)),Number.FromText(Text.End([Test],2)),1))   ),{{"Custom", type date}})
   in
    #"Added Custom"

     

     

    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.

5 Replies

  • CNENFRNL's avatar
    CNENFRNL
    Community Champion
    4 years ago

    Simple enough

    = Table.AddColumn(#"Changed Type", "Start of Month", each Date.From(Text.From([yyyymm])&"01"))

    • Spigaw's avatar
      Spigaw
      Helper III
      4 years ago

      Thank you for the simpler function. However, your column still shows ABC123, and not a calendar, so the data type is still not a date. Is there a workaround for this?

      • CNENFRNL's avatar
        CNENFRNL
        Community Champion
        4 years ago

         

        = Table.AddColumn(#"Changed Type", "Start of Month", each Date.From(Text.From([yyyymm])&"01"), type date)
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Spigaw ,

     

    Based on your M synatx, It seemed that the data type has changed from text to Number firstly by default ,which is not necessary.

     

    So please delete the Changed type step in order to simplify your code and then use the following synatx in the Advanced Editor:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMrBUitWBsAwNlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Test = _t]),
    #"Added Custom" =Table.TransformColumnTypes( Table.AddColumn(Source, "Custom", each Date.From(#date(Number.FromText(Text.Start([Test],4)),Number.FromText(Text.End([Test],2)),1))   ),{{"Custom", type date}})
   in
    #"Added Custom"

     

     

    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.