We have a critical dashboard pulling data from our Excel "database" via power query. I have it set to automatically refresh every 30 minutes and just did a manual refresh. Checking the database, I see that there is new data (several dozen rows) however, the dashboard isn't showing new data for the last 8 hours. When I click "Refresh" or "Refresh All" it does show that it is running the query, but there is still no new data thereafter.
What am I missing? I apologize if I haven't provided all the necessary information, Power Query is new to me as these dashboards were previously in Google Sheets where I can just use IMPORTRANGE() to pull data between sheets.
CODE:
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"
The Excel.CurrentWorkbook(){[Name="fPath"]}[Content]{0}[Column1] gets the current file path from a named range so that it can be updated from any of our computers.
There are also 4 other queries. 1 other coming from the same source, 1 coming from another Excel sheet in the same folder, and 2 coming from online sources. These are also set to refresh every 3-7 minutes.
fpathvariable is pointing to the correct file? – Rory