creating a condiitonal column and want the output to be date,

[Sudi]

I am creating a condiitonal column and want the output to be date, i was using this #date(2023, 9, 1) as syntax in the output column and formatted the new column as date, for some reason the column in throwing an error. What could be wrong.

[jennratten]

@Sudi Have you had a chance to look at the solution provided?  Please consider clicking Accept as Solution if this answered your question or solved your problem.  Thanks!

[Sudi]

Thank you Jen, Posting more details if that helps

• To elaborate The column that i am trying to create a conditional column from has abbreviation of the month i.e NOV, DEC etc
• I am trying to create a new column using condition "contains" this abbreviation and then in the output want to have the column in DD-MM-YYYY format so that i can create hierarchy in the visualization. I can successfully have text of September, October etc however what i need is date format

[jennratten · Accepted solution]

Thanks for the additional info.  You can do something like this...

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nL0U4rViVZyc3UC076OQWDaMSAIyo8E016hflDaByIf6g6mg10DwLS/cwiY9vMPA9Murs5KsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type text}}),
    DateFromMonth = Table.AddColumn(#"Changed Type", "Date", 
        let 
            strMonths = {"JAN", "FEB", "MAR", "APR", "MAY", "JUN", "JUL", "AUG", "SEP", "OCT", "NOV", "DEC"}
        in
        each 
            try
                let intMonth = List.PositionOf ( strMonths, [Month] ) + 1 in
                Date.ToText ( #date(2023,intMonth,1), "dd-MM-yyyy" )
            otherwise null, type text
    )
in
    DateFromMonth

[jennratten]

Hello - it's difficult to say what's wrong without seeing the error message being thrown or the condition and data.  That being said, I would guess that the problem may be the data type like one or more of the below reasons:

• the condition in which the result is not the date specified does not result in a date - therefore formatting it as a date throws an error.
• the values being evaluated by the condition are not appropriate based on the data type.  For example, if you are evaluating 1 = "abc" an error would be thrown because you cannot compare a number to text.

If you need further assistance, please post some sample data along with your script and the error message being generated.