Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Camel
Frequent Visitor

Date in different formats in same column

Hi,

Camel_1-1683915550492.png

 

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

1 ACCEPTED SOLUTION
m_dekorte
Super User
Super User

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!

View solution in original post

3 REPLIES 3
Camel
Frequent Visitor

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 

 

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
Super User
Super User

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!

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.