Excel Power Query在自动刷新或手动刷新时不显示新数据

2022-10-03 21:59| 来源: 网络整理| 查看: 265

我们有一个关键的仪表板，通过power查询从Excel“数据库”中拉取数据。我将其设置为每30分钟自动刷新一次，并且刚刚进行了手动刷新。检查数据库，我看到有新的数据(几十行)，然而，仪表板没有显示过去8小时的新数据。当我单击"Refresh“或"Refresh All”时，它确实显示正在运行查询，但此后仍没有新数据。

我遗漏了什么？如果我没有提供所有必要的信息，我很抱歉，Power Query对我来说是新的，因为这些仪表板以前在Google sheets中，我可以使用IMPORTRANGE()在工作表之间拉取数据。

代码：

let Source = Excel.Workbook(File.Contents(Excel.CurrentWorkbook(){[Name="fPath"]}[Content]{0}[Column1] & "\Collin Walch - Analytics\Inside Sales Dashboards\PestMateDataDump.xlsm"), null, true), SMR_Sheet = Source{[Item="SMR",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(SMR_Sheet, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"First Name", type text}, {"Last Name", type text}, {"Customer Id", Int64.Type}, {"Branch", type text}, {"Service Template", type text}, {"Service Type Id", type any}, {"Primary Lead Source", type text}, {"Secondary Lead Source", type text}, {"Date Created", type date}, {"Time Created", type datetime}, {"Prospect Date Created", type date}, {"Prospect Time Created", type datetime}, {"Became Assigned Prospective Service Type Time", type datetime}, {"Date Contacted", type any}, {"Contacting Sales Rep", type text}, {"First Contact Method", type text}, {"Confirming Sales Rep", type text}, {"Transferred From Sales Rep", type text}, {"Date Transferred", type any}, {"Receiving Sales Rep", type text}, {"Date Received", type any}, {"Prospect Status", type text}, {"Lead Type", type text}, {"Non Sale Reason", type text}, {"Non Lead Reason", type text}, {"Date Sold", type any}, {"Created By User", type text}, {"Structure Type", type text}, {"Not Sold Original Non Sale Reason Employee", type text}, {"Extra Sales Rep Name", type text}, {"Marketing Lead", Int64.Type}, {"Recurring", Int64.Type}}), #"Filtered Rows" = Table.SelectRows(#"Changed Type", each Date.IsInCurrentWeek([Date Created])) in #"Filtered Rows"

Excel.CurrentWorkbook(){Name="fPath"}Content{0}Column1从命名范围中获取当前文件路径，以便可以从我们的任何计算机进行更新。

还有4个其他的查询。1个其他来源，1个来自同一文件夹中的另一个Excel工作表，2个来自联机来源。这些也被设置为每3-7分钟刷新一次。

【本文地址】

公司简介

联系我们