Hi Jacek,

Power Query doesn't have an option to refresh under the conditions you describe. This is a job for VBA. I'll move this post to the VBA forum.

Mynda

Hi Mynda,

thank you very much. 

in PQ there is an option to handle errors using Try function. But i do not know how to implement it. The topic is not for VBA. 
I have 2 possible options:

1. Just do not refresh table if data is not there - leave table empty and do not show error.

I tried with:

let
Source = try (Excel.Workbook(File.Contents("C:UsersadminDesktopNewest Pull requestPQ exampleSourceTable.xlsx"), null, true)) otherwise null,
Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"Name", type text}, {"Surname", type text}})
in
#"Changed Type"

but i am getting that M is not returning list, only null. 

So how to not display error when user is refreshing PQ tables? 
Or how to input nulls into table when error is raised?

2. Create button in VBA to choose path where folder is. If file is not present, do not refresh tables. 

Best,
Jacek

Or i can create empty row in Power Query somehow like here:

// create an empty record
EmptyRecord =
let
FieldNames = Record.FieldNames(FirstRecord),
EmptyValues = List.Repeat({""},Record.FieldCount(FirstRecord))
in
Record.FromList(EmptyValues,FieldNames),

If source file is not there add blank row to the table using Try. 

Please help with implementation,

Jacek

AFAIK refresh is not controlled from within the query. It either refreshes or it doesn't, in order for the query to 'see' if the file is there it needs to refresh the query and then it's too late because the refresh has already taken place.

I'll ask Catalin if he's aware of any way to do this in PQ, but my gut feel is that this isn't possible. You need VBA to check the file, then either refresh or not.

Mynda

Hi Jacek,

Try this:

If source file is not found,  blank table with 2 columns is created: #table({"Name", "Surname"}, {})

O wow thank you very much!!

It is working like a charm! 

Best,

Jacek