excel - 持续时间计算在编辑器中有效，但不在工作表中加载

问题描述

我正在从 .CSV 文件加载数据，并计算持续时间。持续时间在查询编辑器中显示正常，但在加载到工作表时，它显示为空白。这就是它在查询编辑器中的样子：这是生成的电子表格的样子：

请注意，以分钟为单位的时间差计算正确，并且确实传输到电子表格。

关于问题可能是什么的任何想法？

这是电源查询：

/*
    Step0_AllRawData - Load data from files in a folder
    The named range "SourceDirectory" must contain the full path to the directory holding the source .CSV files
*/
let
    Source = Folder.Files("C:\Users\Dee\Desktop\Alice Pharmacy\Ihab\DataFiles-NewFormat"),
    #"Filtered Hidden Files1" = Table.SelectRows(
        Source, 
        each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(
        #"Filtered Hidden Files1", "Transform File (4)", 
        each #"Transform File (4)"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(
        #"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(
        #"Renamed Columns1", {"Source.Name", "Transform File (4)"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(
        #"Removed Other Columns1", "Transform File (4)", 
        Table.ColumnNames(#"Transform File (4)"(#"Sample File (4)"))),
    #"Changed Type" = Table.TransformColumnTypes(
        #"Expanded Table Column1",{
            {"Source.Name", type text}, 
            {"DistrictNumber", Int64.Type}, 
            {"ArrivalTime", type datetime}, 
            {"DisposedDateTime", type datetime}, 
            {"DrugName", type text}, 
            {"prescribedate", type datetime}, 
            {"PrescribedBy", type text}, 
            {"AdministeredDt", type datetime}, 
            {"AdministeredDt1", type datetime}, 
            {"AdministeredBy", type text}, 
            {"drugoutcome", type text}, 
            {"FinalText", type text}, 
            {"", type text}}),
            // Calculate DayOfWeek and time delays...
     #"Add DayOfWeek" = Table.AddColumn(
        #"Changed Type", 
        "DayOfWeek", 
        each Date.DayOfWeekName([prescribedate]), type text),
    #"Add TimeDelayHours" = Table.AddColumn(
        #"Add DayOfWeek", 
        "TimeDelayHours", 
        each (([AdministeredDt]-[prescribedate])), type time ),
    #"Add TimeDelayMins" = Table.AddColumn(
        #"Add TimeDelayHours", 
        "TimeDelayMins", 
        each (([AdministeredDt]-[prescribedate]) * 1440), type number ),
    #"Changed ToNumber" = Table.TransformColumnTypes(
        #"Add TimeDelayMins",{
            {"TimeDelayMins", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(
        #"Changed ToNumber",{
            {"DrugName", "Prescription"}, 
            {"prescribedate", "PrescribeDate"}, 
            {"AdministeredDt", "AdministeredDate"}}),

    #"Removed Columns" = Table.RemoveColumns(
        #"Renamed Columns",{"DistrictNumber", "PrescribedBy", "AdministeredBy", "AdministeredDt1"})
in
    #"Removed Columns"

标签： excelpowerquery

excel - 持续时间计算在编辑器中有效，但不在工作表中加载

问题描述

解决方案

推荐阅读