我有excel表格中的数据,由我们在现场使用的一些软件生成,报告的时间戳如下
2018年5月6日上午6:23:00
由于我们是24小时运营,我需要PowerQuery能够识别从上午12:00:00到上午7:00:00的时间戳属于前一天的夜班。
我面临的问题是,尽管PowerQuery可以将日期/时间作为一种数据类型来处理,但在导入数据时,它似乎会截断时间,因此在我的查询中(显然在我的输出中)上述示例的结果是
2018年5月6日上午12:00:00
我在网上能找到的大部分东西都是关于如何打发时间的——我想保留它!!!
这样做的目的是,我可以在透视表中按时间顺序显示夜班生产的记录。目前,我不得不添加另一个单独包含时间的列,这导致从午夜到早上7:00的数据在晚上7:00到午夜之前出现,而事实上它发生在之后。
干杯
小地毯
编辑:添加我的问题的图片,因为我键入我看不到线程中的图像,所以我希望它们在正确的位置!
作为我的源数据示例,时间戳位于“时间”列中。右边的另一个日期是我正在工作,所以至少我有轮班日期和实际时间在一起。
Source data
下面是我的查询,这里有很多将源位置附加到预定义属性集的操作,基本上我正在努力解决的是“Time”字段被导入,但在小数点后丢失了所有数据,所以我只获取了一个日期。我希望将该时间附加到该日期,以及另一个字段,即如上所述的轮班日期。
let
// Removes unwanted characters.
CharsToRemove = List.Transform({33..45,47,58..126}, each Character.FromNumber(_)),
// The query is all based on the current month's portion of the current 13wk.
Source = Location_Data,
// Set some data fields, not all are changed here as it affects later calculations.
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Group Name", type text}, {"Name", type text}, {"Waste tonnes", type number}, {"Total Ore Tonnes", Int64.Type}, {"Dil cu_pct", type number}, {"Dil au", type number}, {"Dil ag", type number}, {"Dil fe_pct", type number}, {"Dil zn_pct", type number}, {"Density", type number}, {"Material", type text}, {"Type", type text}, {"Active from", Int64.Type}, {"Active to", Int64.Type}, {"Comments", type text}}),
// This steps add the MTD trucking data, and correlates it with our claim grades and density based on two fields, "Name" and "Material".
#"Merged Queries" = Table.NestedJoin(Source,{"Name", "Material"},LoadTrak_Data,{"Load Origin", "Material"},"NewColumn",JoinKind.LeftOuter),
// This step expands the trucking data so we can work with the each column individually, such as truck ID, Date/time, Load Volume, etc.
#"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"Record", "Time", "Dir.", "Operator", "Truck ID", "Load (m3)", "Truck Operator", "Crew", "Shift", "Material", "Load Origin", "Dumped At", "Day", "Shift Time", "Calc Shift"}, {"Record", "Time", "Dir.", "Operator", "Truck ID", "Load (m3)", "Truck Operator", "Crew", "Shift", "Material.1", "Load Origin", "Dumped At", "Day", "Shift Time", "Calc Shift"}),
#"Changed Type3" = Table.TransformColumnTypes(#"Expanded NewColumn",{{"Time", type number}}),
#"Sorted Record low to high" = Table.Sort(#"Changed Type3",{{"Record", Order.Ascending}}),
#"Added Error Volume Column" = Table.AddColumn(#"Sorted Record low to high", "Error Volume", each if [#"Load (m3)"] = null then "28.2" else null ),
#"Changed Error Volume to decimal number" = Table.TransformColumnTypes(#"Added Error Volume Column",{{"Error Volume", type number}}),
#"Added Custom1" = Table.AddColumn(#"Changed Error Volume to decimal number", "DMT", each if [#"Dir."] = null then null else if [#"Load (m3)"] = null then ([Error Volume]*[Density] * 0.7) else [#"Load (m3)"] * [Density] * 0.7),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Level Loaded", each if [Load Origin] = "Empty" then 0 else (Text.Start([Load Origin],4))),
#"Creates shift date" = Table.AddColumn(#"Added Custom2", "Shift Date", each if [Shift Time] is null then null else if [Shift Time] < 0.2916 then [Time] -1 else [Time]),
#"Added Custom" = Table.AddColumn(#"Creates shift date", "Correct location", each if [#"Dir."] = null then null else if ([Shift Date]) < ([Active from]) or ([Shift Date]) > (([Active to])+0.999999) then "No" else "Yes"),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Level Loaded", type number}}),
#"Level Dumped" = Table.AddColumn(#"Changed Type1", "Level Dumped", each if [Dumped At] = "ROM" then 5270 else if [Dumped At] = "PAF" then 5170 else if [Dumped At] = "Paste" then 5270 else if [Dumped At] = "Waste" then 5270 else Text.Start([Dumped At], 4)),
#"Change Level Dumped to decimal number" = Table.TransformColumnTypes(#"Level Dumped",{{"Level Dumped", type number}}),
#"Added TKMs column" = Table.AddColumn(#"Change Level Dumped to decimal number", "TKMs", each if [Dumped At] = "Waste" then (((([Level Dumped] - [Level Loaded])*7)+300)/1000 * [DMT]) else if [Dumped At] = "ROM" then (((([Level Dumped] - [Level Loaded])*7)+150)+300)/1000 * [DMT] else if [Dumped At] = "PAF" then (((([Level Dumped] - [Level Loaded])*7)+150)+300)/1000 * [DMT] else if [Dumped At] = "Paste" then (((([Level Dumped] - [Level Loaded])*7)+150)+300)/1000 * [DMT] else (([Level Dumped] - [Level Loaded])*7)/1000 * [DMT]),
#"Changed TKMs to decimal number" = Table.TransformColumnTypes(#"Added TKMs column",{{"TKMs", type number}}),
#"Filtered Correct Location to remove incorrect duplicates" = Table.SelectRows(#"Changed TKMs to decimal number", each [Correct location] <> "No"),
#"Added load count helper column" = Table.AddColumn(#"Filtered Correct Location to remove incorrect duplicates", "Load", each if [Correct location] = "Yes" then 1 else ""),
#"Filtered Rows1" = Table.SelectRows(#"Added load count helper column", each true),
#"Filtered non-null Group Name rows" = Table.SelectRows(#"Filtered Rows1", each [Group Name] <> null and [Group Name] <> ""),
#"Converts date fields to type date" = Table.TransformColumnTypes(#"Filtered non-null Group Name rows",{{"Active from", type date}, {"Active to", type date}, {"Time", type date}, {"Shift Date", type date}}),
#"Merged with Sched_13wk" = Table.NestedJoin(#"Converts date fields to type date",{"Name", "Material"},Sched_13wk,{"Name", "Material"},"Sched_13wk",JoinKind.LeftOuter),
#"Expanded Sched_13wk" = Table.ExpandTableColumn(#"Merged with Sched_13wk", "Sched_13wk", {"Dil cu_pct", "Material", "Scheduled Tonnes"}, {"Sched_13wk.Dil cu_pct", "Sched_13wk.Material", "Sched_13wk.Scheduled Tonnes"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Expanded Sched_13wk",{{"Shift Time", type time}, {"Day", type date}, {"Time", type date}})
in
#"Changed Type2"
这看起来很可怕,但不知道如何更好地包含它。哈哈,里面的笔记是为了提醒我,当我必须编辑它时,我在做什么。仍然没有完成,想再增加一个负载,这样如果我改变位置,坐在我椅子上的下一个人就会知道查询中发生了什么!
这就是数据在查询输出中的方式,看看所有的时间值都消失了。
Output with no time values.
好吧,我真的希望这有帮助。正如你猜到的,我不是一个程序员!
将报表数据复制到我的excel工作簿中,并键入其他信息,然后进行查询
Report generated by software which is copied and pasted into workbook containing query.
