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- D...
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!

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!