Power Query - Remove Data on Error

[asjones987]

I have a Power Query that pulls from a file that the user types the name in in a cell in Excel. Then Power Query pulls the data data and transforms it and loads it to a table in Excel. However, I have realized if the user types the name of a file that does not exist and refreshes then the data in Excel is still there and there is not a good indication to the user that there was an error. I wold love for the data to be "removed" or some other "big flag" to say there is a problem.

thanks

Alan

 

[alansidman]

How will excel or PQ know that the data in the cell is be be removed. What will trigger that? How will excel know that the file does not exist? Need to have some logic here that can be built into your query.

 

[asjones987]

When the file or folder does not exist when doing a refresh you get an error icon on the Queries and Connections window. When you hover over a message in yellow says "DataSource.Error: Could not find a part of the path". All the other transactions that pull from that first query are then broken.

I want something to say after refresh "things don't work" thus no data or your data is NOT valid so don't use it. The average end user does NOT look in Queries and Connections so they won't know there is a problem. Thus they are using old data from the last time it worked.