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 is missing.

Furthermore, months are in italian and my BI does not recognise some of them as the first 3letters are different form the english translation. Here is an example:

AccountDateamount
Aapr123
Agiu456
Aset789
Adic1859
Bott1354
Bapr153

What can I do to quickly trasform the "date" column in type=date?

thank you for your help

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

2 Replies

  • 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

  • edhans's avatar
    edhans
    Community Champion
    6 years ago

    In Power Query, simply add a new column that has this formula:

     

     

    Date.FromText([Month] & " 2019")

     

     

    A few things:

    1. The three char month is in a column called Month in my formula
    2. I assumed 2019 in the formula. Put whatever you need. 2020 if budgeting for example.
    3. This was using english month abbreviations on the english version. I tried with one of yours - Gui - and the formula returned an error, so this conversion is langauge specific.
    4. Note there is a space before the 2019 in the quotes, so Power Query is converting Jan 2019, not Jan2019.