Excel Power Query条件返回：空表返回指定表头表，有数据则返回原数据

问题说明 #

• 基于Excel制作国家森林火险气象产品，报表包含National Fire Danger Rating System (NFDRS)指数和Remote Automated Weather Stations (RAWS)观测数据
• 数据通过Fire Environment Mapping System (FEMS)以CSV格式获取，目标提取当日14:00的观测数据用于午后/晚间预报
• 当前查询获取当日全天UTC数据，当14:00数据未发布时，Filtered Rows步骤会返回空表，需实现分支逻辑：
  • 空表时：创建带正确表头和首列的空表
  • 非空时：返回正常处理后的数据集

当前M代码 #

let
    Source = Csv.Document(Web.Contents("https://fems.fs2c.usda.gov/api/ext-climatology/download-nfdr?stationIds=361002,361231&endDate="&GetValue("Tomorrow")&"T04:59:59Z&startDate="&GetValue("Today")&"T04:00:00Z&dataFormat=csv&dataset=observation&fuelModels=Y&dateTimeFormat=UTC"),[Delimiter=",", Columns=17, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"stationName", "Station"}, {"observationTime", "Time"}, {"oneHR_TL_FuelMoisture", "1 Hr"}, {"tenHR_TL_FuelMoisture", "10 Hr"}, {"hundredHR_TL_FuelMoisture", "100 Hr"}, {"thousandHR_TL_FuelMoisture", "1000 Hr"}, {"kbdi", "KBDI"}, {"woodyLFI_fuelMoisture", "Woody FM"}, {"herbaceousLFI_fuelMoisture", "Herb FM"}, {"ignitionComponent", "IC"}, {"energyReleaseComponent", "ERC"}, {"spreadComponent", "SC"}, {"burningIndex", "BI"}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Renamed Columns", "Time", Splitter.SplitTextByDelimiter("T", QuoteStyle.Csv), {"Date", "Time"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"1 Hr", type number}, {"10 Hr", type number}, {"100 Hr", type number}, {"1000 Hr", type number}, {"KBDI", type number}, {"Woody FM", type number}, {"Herb FM", type number}, {"IC", type number}, {"ERC", type number}, {"SC", type number}, {"BI", type number}, {"Date", type date}, {"Time", type time}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each Date.IsInCurrentDay([Date]) and [Time] = #time(14, 0, 0)),
    #"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows",{"Station", "Date", "IC", "SC", "ERC", "BI", "KBDI", "1 Hr", "10 Hr", "100 Hr", "1000 Hr", "Woody FM", "Herb FM"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Time","NFDRType", "fuelModelType", "gsi", "NFDRQAFlag"}),
    #"Demoted Headers" = Table.DemoteHeaders(#"Removed Columns"),
    #"Transposed Table" = Table.Transpose(#"Demoted Headers"),
    #"Promoted Headers1" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Renamed Columns1" = Table.RenameColumns(#"Promoted Headers1",{{"ALLEGHENY", "Allegheny"},{"KINZUA","Kinzua"}}),
    #"Added Average Column" = Table.AddColumn(#"Renamed Columns1", "Averages", each List.Average({[Allegheny],[Kinzua]}))
in
    #"Added Average Column"

注1：GetValue()为自定义公式，用于读取已格式化为URL格式的命名单元格日期
注2：刚接触Power Query和M语言，已理解现有代码逻辑

解决方案 #

核心思路是利用Table.IsEmpty()判断过滤后的表是否为空，通过if...then...else实现分支逻辑：

1. 若Filtered Rows为空：手动构造与正常流程输出结构一致的空表，包含正确的首列（指标项）和表头（Allegheny、Kinzua、Averages）
2. 若Filtered Rows非空：执行原有的后续处理步骤

修改后的完整M代码 #

let
    Source = Csv.Document(Web.Contents("https://fems.fs2c.usda.gov/api/ext-climatology/download-nfdr?stationIds=361002,361231&endDate="&GetValue("Tomorrow")&"T04:59:59Z&startDate="&GetValue("Today")&"T04:00:00Z&dataFormat=csv&dataset=observation&fuelModels=Y&dateTimeFormat=UTC"),[Delimiter=",", Columns=17, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"stationName", "Station"}, {"observationTime", "Time"}, {"oneHR_TL_FuelMoisture", "1 Hr"}, {"tenHR_TL_FuelMoisture", "10 Hr"}, {"hundredHR_TL_FuelMoisture", "100 Hr"}, {"thousandHR_TL_FuelMoisture", "1000 Hr"}, {"kbdi", "KBDI"}, {"woodyLFI_fuelMoisture", "Woody FM"}, {"herbaceousLFI_fuelMoisture", "Herb FM"}, {"ignitionComponent", "IC"}, {"energyReleaseComponent", "ERC"}, {"spreadComponent", "SC"}, {"burningIndex", "BI"}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Renamed Columns", "Time", Splitter.SplitTextByDelimiter("T", QuoteStyle.Csv), {"Date", "Time"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"1 Hr", type number}, {"10 Hr", type number}, {"100 Hr", type number}, {"1000 Hr", type number}, {"KBDI", type number}, {"Woody FM", type number}, {"Herb FM", type number}, {"IC", type number}, {"ERC", type number}, {"SC", type number}, {"BI", type number}, {"Date", type date}, {"Time", type time}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each Date.IsInCurrentDay([Date]) and [Time] = #time(14, 0, 0)),
    #判断空表分支 = 
        if Table.IsEmpty(#"Filtered Rows") then
            // 构造空表：首列为指标项，对应正常流程的转置后首列
            let
                #空指标表 = Table.FromRows({{"Station"}, {"Date"}, {"IC"}, {"SC"}, {"ERC"}, {"BI"}, {"KBDI"}, {"1 Hr"}, {"10 Hr"}, {"100 Hr"}, {"1000 Hr"}, {"Woody FM"}, {"Herb FM"}}, {"Column1"}),
                #添加空列 = Table.AddColumns(#空指标表, {
                    "Allegheny", each null,
                    "Kinzua", each null,
                    "Averages", each null
                }),
                #设置类型 = Table.TransformColumnTypes(#添加空列, {
                    {"Column1", type text},
                    {"Allegheny", type number},
                    {"Kinzua", type number},
                    {"Averages", type number}
                }),
                #重命名首列 = Table.RenameColumns(#设置类型, {{"Column1", ""}})
            in
                #重命名首列
        else
            // 执行原有正常处理流程
            let
                #"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows",{"Station", "Date", "IC", "SC", "ERC", "BI", "KBDI", "1 Hr", "10 Hr", "100 Hr", "1000 Hr", "Woody FM", "Herb FM"}),
                #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Time","NFDRType", "fuelModelType", "gsi", "NFDRQAFlag"}),
                #"Demoted Headers" = Table.DemoteHeaders(#"Removed Columns"),
                #"Transposed Table" = Table.Transpose(#"Demoted Headers"),
                #"Promoted Headers1" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
                #"Renamed Columns1" = Table.RenameColumns(#"Promoted Headers1",{{"ALLEGHENY", "Allegheny"},{"KINZUA","Kinzua"}}),
                #"Added Average Column" = Table.AddColumn(#"Renamed Columns1", "Averages", each List.Average({[Allegheny],[Kinzua]}))
            in
                #"Added Average Column"
in
    #判断空表分支

关键说明 #

• Table.IsEmpty(#"Filtered Rows")：检测过滤后的表是否为空
• 空表分支：手动创建包含所有指标项的首列，添加对应空值列并设置匹配的数据类型，确保输出结构与正常流程完全一致
• 非空分支：保留原有处理逻辑，确保数据正常转换和计算

内容的提问来源于stack exchange，提问作者Giric Red Wolf