Endless refresh on import from folder

[davox01]

I have aquery that imports a large number of files from a folder transforms tehm and retunr the resutls to excel. As the number of files is growing its been taking longer and longer to run. After leaving it to run for a few hours this monring I decided to just run on the last 30 days of files rather than the last 9 months.

I left it running for a few hours and still no joy.

I've restarted my computer and tried going inopt teh power query editor  and going through the steps one at at time. It seesm to hang on a very early step. It hangs on the #"Invoke Custom Function1" step below. Theres no error message and the dots are moving above the query so i doesn't look like its timed out.

What is it attempting do in the custom function? I'm only attempting to refresh the preview so it shouldn't take long to run. Isn't the preview just a conversion/transformation of the first file, so if this step is taking ages for the first file will it be taking this long  for each of the 600 files it needs to import?

let
Source = Folder.Files("\\customer\shared\Hove\PartnerCustOps\Reporting\SingleReports\TaskTime\Data\Daily"),
#"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"}),

[v-jingzhang]

Hi @davox01 

You could try the solution provided by Jimmy from the following thread. 

Solved: power query refresh really slow- excel file - Microsoft Power BI Community

The main idea is to connect to a single file to transform data first, just like what you do for your Sample file when using Folder connector. And convert this query into a custom function with the file path as a parameter. For example,

Then connect to the folder, filter rows to only have the files to be imported from. Keep only "Name" and "Folder path" columns, and create a custom column by concatenating them with "&". This creates a File path column. 

Then invoke your custom function fGetFileData to add a column. Use "File path" column for the required FilePath parameter. You will have a table column as a result. After expanding the table column, you will have all data combined in the same query. 

This combines all files data into a query manually. This will probably decrease the refresh time than using the Combine Files feature with Folder connector. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it. Highly appreciate your Kudos!

[davox01]

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"

[davox01]

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

1. 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
2. Waiting for Agent_Group_Report_2022_12_03_1234.xls  (the name of this file changes every rotation
3. xxxkb from Agent_Group_Report_2022_12_01_1234.xls (the name of this file remans the same)