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.
Hi community,
I have a problem which is driving me crazy.
I have some data coming from SAP, in which one of the columns is a date in this format: 16/08/2015 4:00:00 AM. However, when I see the data in power query, it shows as decimal number like this: 2.01902E+13.
I have tried everything to changed this to date, because I need to pivot column "Event type" so that I can see the data from this column as headings, but I get all sorts or errors.
Result:
Hi @pcolas
Change the Regional Settings to "English(US)" and check if the datetime column shows correctly.
Or disable the option below:
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @pcolas,
are you sure you are getting errors? Because they don't show up on your screenshot.
The columns are also a valid DateTime according to the symbol in front of it.
If you mean the null values, that are just values that are not present for that Header handle.
Did this help you or did I answer your question?
Then please give kudos or mark my post as a solution!
My blog: nickyvv.com
Twitter: @NickyvV
Hi @nickyvv
Thank you for your reply.
This is just to show the desireable result, but this is not the actual outcome. Do you know what I mean or should I try to explain this in a different way?
Hello @pcolas
please share you M-code. This error is maybe caused from a wrongly applied column transformation or pivoting function (function that doesn't work with data type. Here an approach how you can force a data transformation by culture
let
Quelle = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKkktLjFU0lGKik8uLS7JzwUyDc30DSz0jQwMTRVMrAwMlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Header handle" = _t, #"Event type" = _t, #"UTC start" = _t]),
ToDateTime = Table.TransformColumns
(
Quelle,
{
{
"UTC start",
each DateTime.FromText(_,"it-IT"),
type datetime
}
}
),
Pivot = Table.Pivot(ToDateTime, List.Distinct(ToDateTime[#"Event type"]), "Event type", "UTC start", List.Max)
in
Pivot
Copy paste this code to the advanced editor in a new blank query to see how the solution works. If this solution fits your need, copy and past a part of it and implement it in your query
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
Hello @Jimmy801
Thank you for your reply.
Please see below my M- Code:
let
Source = Sql.Database("unidataedw01.database.windows.net,1433", "uni-edw"),
cph_VW_SHIPMENT_EVENTS = Source{[Schema="cph",Item="VW_SHIPMENT_EVENTS"]}[Data],
#"Removed Columns" = Table.RemoveColumns(cph_VW_SHIPMENT_EVENTS,{"Event Type", "Event Counter","Version Number", "Sort Order", "Transaction End"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Version Type] = "1")),
#"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"Version Type", "Extract Date"})
in
#"Removed Columns1"
Also below a sample of my data:
Hello @pcolas
the screenshot does not represent the same data source as in your first post. In your first post was an Excelfile, whereas in your last post you are quering SQL and the data in the Transaction start-column does not represent a datetiem value, but I suppose only a YYYY/MM-format, meaning the frist 4 digits is the month and the next two the month. But I can't see any day or time-part.
Jimmy
Hi @nickyvv
My data looks like this:
I want to convert column transaction start to date and then make this table as pivot, so that Event description is on the headers.
Covering 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 |
---|---|
100 | |
54 | |
21 | |
12 | |
11 |