PowerBI中如何处理毫秒 [英] How to deal with milliseconds in PowerBI
问题描述
我正在使用机器人,我需要按日期时间列对事件进行排序,当我从数据库中导入该列时,我具有以下格式:
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屋!