Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
In Power Query, simply add a new column that has this formula:
Date.FromText([Month] & " 2019")
A few things:
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
99 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |