Date in different formats in same column

[Camel]

Hi,

I have a column with the following date value. I need a query in power query so that date with short date in a commanformat is shown. example-
05-12-2023
30-04-2023

Note- Dax is not possible as it is a date erroe

[Camel]

Lets say there is another column in this table with any other random value say a number 1 to 10 , how can i modify the query to target just that one column @m_dekorte . can you help me with this 

[m_dekorte]

Hi @Camel,

I'm not sure I understand your reply.

The provided solution will only be applied to the column listed as first argument in the TransformationList, in this case the collumn called "Date".

If you're encountering a specific issue with the proposed solution, please provide more details to illustrate the problem. Thanks.

Ps. If this helps solve your query please mark this post as Solution, thanks!

[m_dekorte · Accepted solution]

Hi @Camel,

Give this a go. You can copy the full script into a new blank query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pc67DYAwDEXRVRA1yPaLA0k6BkCiR+y/BiGBAjAV7bnyZ11b9r2gB8M1oglju3VPdNFAhYG+opIwFQtJYmJuprkER5AjIAfmXyEQquf/ii/Vx6xmGOhcJHIPSvFrkzeDUjDdkztPx9fp4ZqAXmHbAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
    SetDateType = Table.TransformColumns( Source,
        {
            {"Date", each 
                [ 
                    d = Splitter.SplitTextByWhitespace()(_), 
                    r = try if Text.Length( d{2}?? "" ) >1 then Date.FromText( d{0}, [Culture="en-US"]) else null 
                        otherwise Date.FromText( d{0}, [Culture="en-GB"])
                ][r], type date
            }
        })
in
    SetDateType

Ps. If this helps solve your query please mark this post as Solution, thanks!