Forum Discussion
Reduce loading time during refresh
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:
- DateTime.LocalNow() is the M command for todays date
- 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) ))
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- 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.
I forgot to write the old data is not refreshed with refresh all and have a macro to refresh it
