如何在PowerBI中处理毫秒 [英] How to deal with milliseconds in PowerBI
问题描述
我正在与一个机器人一起工作,我需要按datetime列对事件进行排序,当我从数据库中导入该列时,我的格式是:
I am working with a bot, and I need to sort the event by a datetime column, when I import the column from my database I have this format:
2017-10-19T14:26:57.2349278Z
导入后在Power BI中,并将数据类型更改为日期时间,我得到以下信息:
after importing in power BI and changing the data type to date time I get this:
10/19/2017 02:26:57 PM
所有尾随毫秒均被截断,但我需要它们对事件进行正确排序,因为某些事件会发生在同一秒内。
all the trailing milliseconds are truncated, but I need them to sort correctly the events, because some events occurs in the same second.
有什么想法吗?
谢谢!
Any body any idea? Thanks!
推荐答案
从以您的格式显示的日期示例表开始,将其加载到Power BI中后,结果。
Starting with a sample table of dates in your format, when loaded into Power BI, this is the result.
单击导航步骤,然后单击单击添加列->自定义列。弹出提示,询问您是否要插入步骤,单击插入。
Click on the Navigation step and then click on Add Column -> Custom Column. A prompt will pop up asking if you want to insert a step, click Insert.
在提示符下,输入以下公式。
In the prompt, enter the following formula.
Text.Start([#"Timestamp"], 4) &
Text.Middle([#"Timestamp"], 5, 2) &
Text.Middle([#"Timestamp"], 8, 2) &
Text.Middle([#"Timestamp"], 11, 2) &
Text.Middle([#"Timestamp"], 14, 2) &
Text.Middle([#"Timestamp"], 17, 2) &
Text.Middle([#"Timestamp"], 20, 7)
这将为您提供一列,该列可用于按精确的日期时间对表中的项目进行排序。请注意,自定义列公式要求原始日期字符串的格式与您的问题完全相同。
This gives you a column that can be used to sort the items in the table by a precise datetime. Note that the custom column formula requires the raw date string to be in the exact same format as in your question.
这篇关于如何在PowerBI中处理毫秒的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!