cancel
Showing results for 
Search instead for 
Did you mean: 

Power Query not properly handling error when using File.Contents and file not found

Product: Power BI Desktop (x64)

Version: 2.54.4970.961

 

I'm using File.Contents function for reading a file that doesn't exists in my filesystem. I expect it to return an error. Graphically it does, as shown in the image below:

 

Power Query Error File Not Found File.Contents function

 

The code for the query above:

let
    Source = File.Contents("C:\not-existent-file.txt")
in
    Source

Although if a try to catch the error programmatically, by using a try expression, it returns a record, with the HasError field set to false, as shown in the image below:

 

Power Query try expression not properly handling errors when using File.Contents function

 

The code for the query above:

let
    Source = try File.Contents("C:\not-existent-file.txt")
in
    Source

I don't think that is the expected behavior.

 

I actually expect the HasError field to be set to true and an Error field containing an Error record. So I could programmatically deal with it.

Status: Accepted
Comments
Community Support Team

Hi @avilapedro,

 

I have reported this issue to the Product Team: CRI 58221128. I will update the news here.

 

Best Regards,

Dale

Established Member
Status changed to: Accepted
 
Regular Visitor

Hi there. This is due to the fact that the result of File.Contents is "lazy". In other words, the file isn't actually read unless it's necessary. Since the expression you pasted doesn't actually need to read the file (until you navigate into file content within the error record), as far as M is concerned there's no error. Strange, I know. But that's how it works today.

 

Here's an example of how to force M to read the file, leading to the behavior you're wanting:

 

try Binary.Length(File.Contents("D:\NoSuchFile.file"))

 

Hope that helps.

Ehren

Occasional Visitor

Got it! That makes sense.

 

But I would have a wider range of possible kinds of errors that could be thrown by the Binary.Length function (if the file is found). So I should check like this:

 

let
    content = try Binary.Length( File.Contents("C:\.rnd") )
in
    if ( content[HasError] ) then
        if ( content[Error][Reason] = "DataSource.Error" )
        then "Not Found"
        else error content[Error]
    else content[Value]

 

 

Am I right?

Regular Visitor

Yes, there's a range of possible errors (including different DataSource.Errors, too). As a starting point, though, I'd probably go with something simple and add handling for additional cases as they arise.

 

let
file = File.Contents("C:\.rnd"),
length = Binary.Length(file),
in
try (if length >= 0 then file else null) otherwise "An error occurred"

 

Or, since you're likely not just accessing the file as binary data, but doing further processing on it, you could do that further processing inside of a "try" or "try...otherwise" expression. For example, if you were processing it as a CSV file:

 

try Csv.Document(File.Contents("C:\.rnd"))

This will produce an error if the file doesn't exist, since Csv.Document will attempt to read the file in order to construct its result. The same should be true for our other data source functions.

 

Ehren