cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
anna7
New Member

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

1 ACCEPTED SOLUTION

Accepted Solutions
Super User I
Super User I

Re: field date

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

View solution in original post

2 REPLIES 2
Super User II
Super User II

Re: field date

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.

 

2019-12-17 06_39_01-Untitled - Power Query Editor.png



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Super User I
Super User I

Re: field date

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

View solution in original post

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors