Forum Discussion

Camel's avatar
Camel
Frequent Visitor
3 years ago
Solved

Date in different formats in same column

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

Need Help
Tips and Tricks
  • m_dekorte's avatar
    m_dekorte
    3 years ago

    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!

3 Replies

  • m_dekorte's avatar
    m_dekorte
    Resident Rockstar
    3 years ago

    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!

  • Camel's avatar
    Camel
    Frequent Visitor
    3 years ago

    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's avatar
      m_dekorte
      Resident Rockstar
      3 years ago

      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!