Format date directly in the function

[Spigaw]

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.

[CNENFRNL · Accepted solution]

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

[v-eqin-msft · Accepted solution]

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.

[CNENFRNL]

Simple enough

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

[Spigaw]

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?

[Syndicate_Admin]

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

[CNENFRNL · Accepted solution]

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