Forum Discussion

anna7's avatar
anna7
New Member
6 years ago
Solved

field date

hello i have imported in power BI a db where the date is written in letter. of course when i try to change in BI the type of the field and put it as date, the system gives me an error as the year i...
Need Help
  • Jimmy801's avatar
    Jimmy801
    6 years ago

    Hello anna7 and welcome to our community

     

    supposing that your database is dated this year, you can apply a Date.FromText applying italian culture.

    Here the complete example

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUUosKAKShkbGSrE6EJH0zFIgaWJqBhcpTi0BkuYWlnCRlMxkkC4LU4iQE5CTXwJSZGhsagIXgpptCjQ7FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Account = _t, Date = _t, amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes
    (
        Source,
        {{"Account", type text}, {"Date", type text}, {"amount", Int64.Type}}
    ),
    ChangeDate= Table.TransformColumns
    (
        #"Changed Type", 
        {{"Date", each Date.FromText(_&"2019", "it-IT"), type date}}
    )
in
    ChangeDate

     

    Copy paste this to the advanced editor to see the result

     

    If this post helps or solves your problem, please mark it as solution.
    Kudos are nice to - thanks
    Have fun

    Jimmy

Jimmy801's avatar
Jimmy801
Community Champion
6 years ago

Hello anna7 and welcome to our community

 

supposing that your database is dated this year, you can apply a Date.FromText applying italian culture.

Here the complete example

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUUosKAKShkbGSrE6EJH0zFIgaWJqBhcpTi0BkuYWlnCRlMxkkC4LU4iQE5CTXwJSZGhsagIXgpptCjQ7FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Account = _t, Date = _t, amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes
    (
        Source,
        {{"Account", type text}, {"Date", type text}, {"amount", Int64.Type}}
    ),
    ChangeDate= Table.TransformColumns
    (
        #"Changed Type", 
        {{"Date", each Date.FromText(_&"2019", "it-IT"), type date}}
    )
in
    ChangeDate

 

Copy paste this to the advanced editor to see the result

 

If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun

Jimmy