Forum Discussion
Endless refresh on import from folder
I'm hitting an error:
Formula.Firewall: Query 'fGetFileData' references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.
let
Source = Folder.Files("\\customer\shared\Hove\PartnerCustOps\Reporting\SingleReports\TaskTime\Data\Daily"),
#"Added custom" = Table.AddColumn (Source,"File Path", each [Folder Path] & [Name]),
#"Invoke Custom Function1" = Table.AddColumn(#"Added custom", "fGetFileData", each fGetFileData ([File Path]))
in
#"Invoke Custom Function1"
This is my transfmation query 'fGetFileData'
(filepath as text)=>
let
Source = Excel.Workbook(File.Contents(filepath),null,true),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1",{"Source.Name", "Transform File"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type text}, {"Column17", type text}, {"Column18", type text}, {"Column19", type text}, {"Column20", type text}, {"Column21", type text}, {"Column22", type text}, {"Column23", type text}, {"Column24", type text}, {"Column25", type text}, {"Column26", type text}, {"Column27", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"", type text}, {"Sign In Duration", type text}, {"Inbound Calls Handled", type text}, {"Inbound Average Handling Time", type text}, {"Talk Time", type text}, {"Wrap-Up Time", type text}, {"On hold Time", type text}, {"Outbound Calls", type text}, {"Outbound Time", type text}, {"Count of Agent Status (Back Office)", type text}, {"Count of Agent Status (Outbound)", type text}, {"Count of Agent Status (Meeting)", type text}, {"Count of Agent Status (Break)", type text}, {"Count of Agent Status (Training)", type text}, {"Count of Agent Status (Wrap-Up)", type text}, {"Count of Agent Status (Task Time)", type text}, {"Count of Agent Status (Lunch)", type text}, {"Count of Agent Status (Agent Transfer)", type text}, {"Count of Agent Status (TU Duties)", type text}, {"Count of Agent Status (Messaging)", type text}, {"Count of Agent status (Inbound Cust Email)", type text}, {"Count of Agent status (Admin/Communications)", type text}, {"Count of Agent status (TaskTime Unscheduled)", type text}, {"Count of Agent status (Medical Back Office)", type text}, {"Count of Agent status (Social Media)", type text}, {"Count of Agent status (Assistance required)", type text}, {"Count of Agent status (Point of Contact)", type text}, {"Count of Agent status (IT Issues)", type text}, {"Count of Agent status (Complaints)", type text}, {"Number of Logons", type text}, {"Outbound Average Handling Time", type text}, {"Consultation or Transfer", type text}, {"Number of Calls in Fault", type text}, {"Maximum of Agents Log-in", type text}, {"First Logged in", type text}, {"Last Logged off", type text}, {"Total Time:Back Office", type text}, {"Total Time:Outbound", type text}, {"Total Time:Meeting", type text}, {"Total Time:Break", type text}, {"Total Time: Toilet Break", type text}, {"Total Time:Training", type text}, {"Total Time:Lunch", type text}, {"Total Time:Task Time", type text}, {"Total Time:Available Time", type text}, {"Total Time: Agent Transfer", type text}, {"Total Time: TU Duties", type text}, {"Total Time: Messaging", type text}, {"Total Inbound Cust Email", type text}, {"Total Admin/Communications", type text}, {"Total TaskTime Unscheduled", type text}, {"Total Medical Back Office", type text}, {"Total Social Media", type text}, {"Total Assistance required", type text}, {"Total Point of Contact", type text}, {"Total IT Issues", type text}, {"Total Complaints", type text}, {"Outbound Attempts", type text}, {"Sign In Duration (inc. Lunch)", type text}, {"Column61", type any}}),
#"Renamed Columns3" = Table.RenameColumns(#"Changed Type1",{{Table.ColumnNames(#"Changed Type1"){0}, "Source"}}),
#"Renamed Columns" = Table.RenameColumns(#"Renamed Columns3",{{"", "Name"}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Renamed Columns", "Name", Splitter.SplitTextByEachDelimiter({"("}, QuoteStyle.Csv, false), {"Name.1", "Name.2"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Name.1", type text}, {"Name.2", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type2",")","",Replacer.ReplaceText,{"Name.2"}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Replaced Value", "Source", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Source.1", "Source.2", "Source.3", "Source.4", "Source.5", "Source.6"}),
#"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter1",{"Source.1", "Source.2", "Source.3", "Source.4", "Source.6"}),
#"Renamed Columns4" = Table.RenameColumns(#"Removed Columns",{{"Source.5", "Date"}}),
#"Renamed Columns2" = Table.RenameColumns(#"Renamed Columns4",{{"Name.1", "Name"}, {"Name.2", "ID"}}),
#"Filtered Rows" = Table.SelectRows(#"Renamed Columns2", each ([Name] <> null and [Name] <> "" and [Name] <> "Total")),
#"Changed Type3" = Table.TransformColumnTypes(#"Filtered Rows",{{"Inbound Calls Handled", Int64.Type}, {"Outbound Calls", Int64.Type}, {"Count of Agent Status (Back Office)", Int64.Type}, {"Count of Agent Status (Outbound)", Int64.Type}, {"Count of Agent Status (Meeting)", Int64.Type}, {"Count of Agent Status (Break)", Int64.Type}, {"Count of Agent Status (Training)", Int64.Type}, {"Count of Agent Status (Wrap-Up)", Int64.Type}, {"Count of Agent Status (Task Time)", Int64.Type}, {"Count of Agent Status (Lunch)", Int64.Type}, {"Count of Agent Status (Agent Transfer)", Int64.Type}, {"Count of Agent Status (TU Duties)", Int64.Type}, {"Count of Agent Status (Messaging)", Int64.Type}, {"Count of Agent status (Inbound Cust Email)", Int64.Type}, {"Count of Agent status (Admin/Communications)", Int64.Type}, {"Count of Agent status (TaskTime Unscheduled)", Int64.Type}, {"Count of Agent status (Medical Back Office)", Int64.Type}, {"Count of Agent status (Social Media)", Int64.Type}, {"Count of Agent status (Assistance required)", Int64.Type}, {"Count of Agent status (Point of Contact)", Int64.Type}, {"Count of Agent status (IT Issues)", Int64.Type}, {"Count of Agent status (Complaints)", Int64.Type}, {"Number of Logons", Int64.Type}, {"Consultation or Transfer", Int64.Type}, {"Number of Calls in Fault", Int64.Type}, {"Maximum of Agents Log-in", Int64.Type}, {"Outbound Attempts", Int64.Type}, {"Date", type date}, {"Sign In Duration", type time}, {"Inbound Average Handling Time", type time}, {"Talk Time", type time}, {"Wrap-Up Time", type time}, {"On hold Time", type time}, {"Outbound Time", type time}, {"Outbound Average Handling Time", type time}, {"First Logged in", type time}, {"Last Logged off", type time}, {"Total Time:Back Office", type time}, {"Total Time:Outbound", type time}, {"Total Time:Meeting", type time}, {"Total Time:Break", type time}, {"Total Time: Toilet Break", type time}, {"Total Time:Training", type time}, {"Total Time:Lunch", type time}, {"Total Time:Task Time", type time}, {"Total Time:Available Time", type time}, {"Total Time: Agent Transfer", type time}, {"Total Time: TU Duties", type time}, {"Total Time: Messaging", type time}, {"Total Inbound Cust Email", type time}, {"Total Admin/Communications", type time}, {"Total TaskTime Unscheduled", type time}, {"Total Medical Back Office", type time}, {"Total Social Media", type time}, {"Total Assistance required", type time}, {"Total Point of Contact", type time}, {"Total IT Issues", type time}, {"Total Complaints", type time}, {"Sign In Duration (inc. Lunch)", type time}})
in
#"Changed Type3"
I'm still getting the firewall issue in power query editor
Formula.Firewall: Query 'fGetFileData' references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.
But when i went back inot the spreadsheet the query started to run, it is still crawling along. I can see the tables going through the query at the bottom of the queries and connections tab .
The bottom line has the circle of dots turning and the name of the query and then underneath it goes through a loop
- 4000 rows from 'Energy Agent Group Report$' (energy agent is the the first line of each file) the number of rows jumps up by ~300 each time i see this line
- Waiting for Agent_Group_Report_2022_12_03_1234.xls (the name of this file changes every rotation
- xxxkb from Agent_Group_Report_2022_12_01_1234.xls (the name of this file remans the same)