Add custom column with date based on another column

[goncz]

I want to add a custom column in query editor that contains dates on the format "14/10/2020".

The specific date should depend on another column in the dataset with dates on the format "14/10/2020 00:00:00". So the new column should essentially remove the "00:00:00".

I've tried the following code, which always returns null:

= if[Endtime] = "01/01/2020 00:00:00" then [Date] = "01/01/20" else null

How should I go about this? 

[AlB · Accepted solution]

Hi @goncz 

Make sure the EndTime column is of type datetime. Then simply crete a custom column converting to date. Place the following M code in a blank query to see the steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUByIjAyMDBQMDKzBSio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [EndTime = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"EndTime", type datetime}}),

    #"Added Custom" = Table.AddColumn(#"Changed Type", "Date", each Date.From([EndTime]), type date)
in
    #"Added Custom"

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[v-yiruan-msft · Accepted solution]

Hi @goncz ，

You can copy Endtime column and set the data ype of copied column as Date:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtA3BCITBQMrAxBSio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Endtime = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Endtime", type datetime}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Endtime", "Endtime - Copy"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Duplicated Column",{{"Endtime - Copy", type date}})
in
    #"Changed Type1"

Best Regards

[AlB · Accepted solution]

Hi @goncz 

Make sure the EndTime column is of type datetime. Then simply crete a custom column converting to date. Place the following M code in a blank query to see the steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUByIjAyMDBQMDKzBSio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [EndTime = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"EndTime", type datetime}}),

    #"Added Custom" = Table.AddColumn(#"Changed Type", "Date", each Date.From([EndTime]), type date)
in
    #"Added Custom"

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[amitchandak]

@goncz , Try like

if[Endtime] = DateTime.FromText("2020-01-01 00:00:00") then Date.FromText("2020-01-01") else null

[Gabriel_Walkman]

if [Endtime] <> null then [Date] ? Text.Left(Text.From([Endtime]),10))

I haven't checked in BI so I can have bugs, but I hope it makes you move forward. I suspect your "Endtime" isn't in text format, so you can't compare it to text. Check out the #date() and #datetime() if you need a specific date comparison.

[goncz]

Thanks @Gabriel_Walkman . The Endtime is in Date/Time format. Your code suggestion does not work, I get "Token Else expected" in:

if [Endtime] <> null then [Date] ? Text.Left(Text.From([Endtime]),10))