用Excel Power Query从网上每日获取并自动更新的数据表

后端开发 2026-07-09

背景： 我在野外火灾防控领域工作，想帮助一位希望追踪预测火情指数并将其与随时间的观测指数进行对比的消防官员。我们使用的系统是FEMS，但它不记录历史预测，只记录观测值。另一个工作簿已经使用Power Query从网页获取当前数据。

详情： FEMS的天气和火情指数每小时和每日更新，我们通过以下Power Query进行拉取——请注意，URL指向一个CSV文档：

let
    Source = Csv.Document(Web.Contents("https://fems.fs2c.usda.gov/api/ext-climatology/download-nfdr-daily-summary/?dataset=all&startDate="&GetValue("Yesterday")&"&endDate="&GetValue("Tomorrow")&"&dataFormat=csv&stationIds=400101,159901&fuelModels=Y"),[Delimiter=",", Columns=25, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Removed Columns" = Table.RemoveColumns(#"Promoted Headers",{"FuelModel", "Min1HrFMTime", "Min10HrFMTime", "Min100HrFMTime", "Min1000HrFMTime", "GSI", "MaxICTime", "MaxERCTime", "MaxSCTime", "MaxBITime", "NFDRQAFlag"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"IC", type number}, {"SC", type number}, {"ERC", type number}, {"BI", type number}, {"KBDI", type number}, {"1HrFM", type number}, {"10HrFM", type number}, {"100HrFM", type number}, {"1000HrFM", type number}, {"WoodyFM", type number}, {"HerbFM", type number}, {"ObservationTime", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"StationName", "Station"}, {"ObservationTime", "Date"}, {"NFDRType", "NFDR Type"}, {"1HrFM", "1 Hr"}, {"10HrFM", "10 Hr"}, {"100HrFM", "100 Hr"}, {"1000HrFM", "1000 Hr"}, {"WoodyFM", "Woody FM"}, {"HerbFM", "Herb FM"}}),
    #"Reorder Columns" = Table.ReorderColumns(#"Renamed Columns",{"Station", "Date", "IC", "SC", "ERC", "BI", "KBDI", "1 Hr", "10 Hr", "100 Hr", "1000 Hr", "Woody FM", "Herb FM"})
in
    #"Reorder Columns"

GetValue() 是

(rangeName) => 
    Excel.CurrentWorkbook(){[Name=rangeName]}[Content]{0}[Column1]

并且它引用命名单元格，公式为 =TEXT(TODAY()+1,"YYYY-mm-dd")（明天用+1，昨天用 -1）。NFDRS Type O是 Observed，F是 Forecast。若你今天运行，4/26将为O，4/27与 4/28将为F。明天，4/27将为O，4/28与 29将为F。随着时间推移，循环往复。从 https://fems.fs2c.usda.gov/download 选取数据时，观测值可以追溯相当长的时间，但预测值只能从今天起向后可用。

尝试： 我尝试建立一个日历表，使用IFERROR查看生成的表并根据日期和NFDR Type在某个单元格放入一个选定的索引，但在出错时会自我引用。第一天还算可以，但第二天就彻底失败。我尝试在Power Query中进行筛选，然后做一个表将新数据追加到末尾，但Power Query也不允许你实现这样的循环。

请求： 在不使用宏（出于安全原因不允许）的前提下，是否有办法把它构建成能够跟踪一个选定的Forecast指标的结构？

4/27的示例表：

Date	Sta1 O IC	Sta1 F IC	Sta2 O IC	Sta2 F IC
4/26	Obs#		Obs#
4/27		Fcst#		Fcst#
4/28		Fcst#		Fcst#

4/28时更新后看起来是

Date	Sta1 O IC	Sta1 F IC	Sta2 O IC	Sta2 F IC
4/26	Obs#		Obs#
4/27	Obs#	Fcst#	Obs#	Fcst#
4/28		Fcst#		Fcst#
4/29		Fcst#		Fcst#

再到4/29时...

Date	Sta1 O IC	Sta1 F IC	Sta2 O IC	Sta2 F IC
4/26	Obs#		Obs#
4/27	Obs#	Fcst#	Obs#	Fcst#
4/28	Obs#	Fcst#	Obs#	Fcst#
4/29		Fcst#		Fcst#
4/30		Fcst#		Fcst#

如果需要，我可以分别拉取观测和预测，但我始终想不通如何设置，使得一旦从表中提取，它就是一个数值而不是再也无法工作的公式。

解决方案

如果我理解正确，你想保留历史预测。你可以通过自引用来解决这一点（更多细节见 https://exceleratorbi.com.au/self-referencing-tables-power-query/）。

本质上，按原样将表加载到Excel。然后选中该表并选择 Data > From Table/Range。这将在工作簿中为加载的表创建一个新的PQ。

把你的第一个查询（CSV那个）称作 "Latest"，新建的查询（来自Excel的）称作 "Historic"。在Historic中，筛选至今日及之前。然后在Latest中，筛选至今日之后。接着把Historic查询追加到Latest查询之后。最后，将Historic设置为仅连接查询（即它不会加载到工作簿中）。

以上假设过去的观测并不会随时间改变；如果会改变，你需要做更多微调，比如进行合并等…

小提示：在PQ中可以这样获取昨天和明天的日期：

let
    yesterday =  Date.ToText( DateTime.Date(Date.AddDays(DateTime.LocalNow(), -1)), "yyyy-MM-dd"),
    tomorrow  =  Date.ToText( DateTime.Date(Date.AddDays(DateTime.LocalNow(),  1)), "yyyy-MM-dd"),
    Source = Csv.Document(Web.Contents("https://fems.fs2c.usda.gov/api/ext-climatology/download-nfdr-daily-summary/?dataset=all&startDate="&yesterday&"&endDate="&tomorrow&"&dataFormat=csv&stationIds=400101,159901&fuelModels=Y"),[Delimiter=",", Columns=25, Encoding=1252, QuoteStyle=QuoteStyle.None]),

Excel