field date

[anna7]

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:

Account	Date	amount
A	apr	123
A	giu	456
A	set	789
A	dic	1859
B	ott	1354
B	apr	153

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

thank you for your help

[Jimmy801 · Accepted solution]

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]

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.