Reduce loading time during refresh

[Wlandim]

I have the a issue when I use load "From Folder" with a lot of worksheets, every time I need to refresh I have 365 worksheets with 4 sheet each, that is a lot of processing.

I need only one sheet to be loaded, but during the processing is possibel to see all other sheets being refreshed.

I tryed many workarounds but power query reads all the four sheets of each worksheet before filtering my sheet of interest.

I used the function below.

let (Path) =>
// Source = Excel.Workbook(File.Contents("C:\Users\Landim\Documents\microsoft\2018-01-01.xlsx"), null, true),
Source = Excel.Workbook(File.Contents(Path), null, true), // All the sheets in each worksheet is loaded
Query = Source{[Item="Sheet1",Kind="Sheet"]}[Data], // here I filter the sheets I Want
#"Promoted Headers" = Table.PromoteHeaders(Query, [PromoteAllScalars=true]), //Start the navigation
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Dia", type date}, {"Hora", type datetime}, {"Qtd", Int64.Type}})
in
#"Changed Type"

[Seward12533]

You can disable refresh of individual tables with the PowerQuery interface by right clicking over the query name and unchecking "Include in Report Refresh"  Since your pulling in all at once I would duplicat your query and modfiy one to refresh all except the one(s) you want to update and the other to referesh only the ones you want to update.  You can update this with filter paramaters when editing the query and you see the list of files in the directory. 

Some alternate approaches. have one query pull in all files with file dates older than n days (say 30, 7, 15 or whatever) from today and set that one to not refresh with the report and have the the other only import recently udpated files.   

The trick to relative date filtering is to filter for a specific date and then go edit the M (can do it from the Advanced Editor or the Forula bar. 

Tips: 

1. DateTime.LocalNow() is the M command for todays date
2. Date.AddDays is the function to add or subract days

If I pull up a list of files on my desktop and then filter for one date I get the followign in the formula bar

= Table.SelectRows(Source, each ([Date modified] = #datetime(2017, 12, 10, 0, 59, 0.4835081)))

I edit this to get things modified in the last 30days

= Table.SelectRows(Source, each ([Date modified] >= Date.AddDays(DateTime.LocalNow(),-30) ))

[Wlandim]

Thank you Seward!

I did a dinamic date table to filter the two tables, a table with old values and a table with fresh ones.

Besides that, the timing to load the fresh datas is to high.

Below you can see the code I used:

This is for the old datas:

let

Source = #"Pesquisa Base",
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Name.1", type date}}),
// #"Filtered Rows1" = Table.SelectRows(#"Changed Type", each [Name.1] > #date(2018, 1, 2)),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each (List.ContainsAny(startdate,{[Name.1]}))),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Name.1"}),
#"Invoked Custom Function" = Table.AddColumn(#"Removed Columns", "GetFiles", each GetFiles([Path])),
#"Expanded GetFiles" = Table.ExpandTableColumn(#"Invoked Custom Function", "GetFiles", {"Dia", "Hora", "Qtd"}, {"Dia", "Hora", "Qtd"}),
#"Removed Columns2" = Table.RemoveColumns(#"Expanded GetFiles",{"Path"})
in
#"Removed Columns2"

This is for the fresh ones:

let

Source = #"Pesquisa Base",
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Name.1", type date}}),
// #"Filtered Rows1" = Table.SelectRows(#"Changed Type", each [Name.1] > #date(2018, 1, 2)),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each not (List.ContainsAny(startdate,{[Name.1]}))),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Name.1"}),
#"Invoked Custom Function" = Table.AddColumn(#"Removed Columns", "GetFiles", each GetFiles([Path])),
#"Expanded GetFiles" = Table.ExpandTableColumn(#"Invoked Custom Function", "GetFiles", {"Dia", "Hora", "Qtd"}, {"Dia", "Hora", "Qtd"}),
#"Removed Columns2" = Table.RemoveColumns(#"Expanded GetFiles",{"Path"})
in
#"Removed Columns2"

Here we have the 'startdate' for the fresh datas

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type1" = Table.TransformColumnTypes(Source,{{"Data", type date}}),
Data = #"Changed Type1"[Data]
in
Data

[Wlandim]

I forgot to write the old data is not refreshed with refresh all and have a macro to refresh it

[Seward12533]

Did you disable update on refresh for the older ones? I would recommend migrating to a database even if it’s local to your machine dealing with that many local files will be very slow no matter what you do. Databases are fast especially since PBI can push the WorkPad into the server.