Error handling is primary source is absent

[Artur_]

hi,

I would like to build a query that normally pulls the data from "C:\pathA\fileA" but if fileA is not out there, it pulls from "C:\pathB\fileB". I've tried the following but nothing works... It throws a DataSource.Error: Could not find file "C:\pathA\fileA".

Source = try Csv.Document(File.Contents("C:\pathA\fileA.csv"),[Delimiter=",", Columns=36, Encoding=1252, QuoteStyle=QuoteStyle.None]) otherwise Csv.Document(File.Contents("C:\pathB\fileB.csv"),[Delimiter=",", Columns=36, Encoding=1252, QuoteStyle=QuoteStyle.None])

    Source = Csv.Document(File.Contents("C:\pathA\fileA.csv"),[Delimiter=",", Columns=36, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    AlternativeOutput = Csv.Document(File.Contents("C:\pathB\fileB.csv"),[Delimiter=",", Columns=36, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    TestForError = try Source,
    Output = if TestForError[HasError] then AlternativeOutput else #"Promoted Headers",

    Source = Csv.Document(File.Contents("C:\pathA\fileA.csv"),[Delimiter=",", Columns=36, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    TestForError = try Source,
    Output = if TestForError[HasError] then Csv.Document(File.Contents("C:\pathB\fileB.csv"),[Delimiter=",", Columns=36, Encoding=1252, QuoteStyle=QuoteStyle.None]),
else #"Promoted Headers",

 your help would be highly appreciated!

Artur

[artemus]

This should work:

let
    Source = Csv.Document(Text.FromBinary(File.Contents("C:\pathA\fileA.csv"), TextEncoding.Windows),[Delimiter=",", Columns=36, QuoteStyle=QuoteStyle.None]),
    #"Alternative Source" = Csv.Document(Text.FromBinary(File.Contents("C:\pathB\fileB.csv"), TextEncoding.Windows),[Delimiter=",", Columns=36, QuoteStyle=QuoteStyle.None]),
    Output = try Source otherwise #"Alternative Source"
in
    Output

[Artur_]

Thank you, but this is still not working:

DataSource.Error: Could not find file 'C:\pathA\fileA.csv'

Seems M code can't consider this as an error?

[Smauro · Accepted solution]

Hi Artur,

Your issue is with File.Contents: It gets an error trying to access the file but transforms this error into a binary anyway. So, when try is executed it gets a value but without errors, since the error is inside this value.
Since error are transferable, I'm not sure how this happens.
It is -- for me -- a bug of the pqengine evaluation system.

Aaaaanyway,

Try using file.contents alone, something like:

Source = try (try File.Contents(filestring1))[Value]{0},
Source_final = if Source[HasError] then File.Contents(filestring2) else File.Contents(filestring1)
Nah, I actually tested this. It won't work since the error is still inside the binary. It happens on accessing it. This will work:

Source = try Binary.Length((try File.Contents(filestring1))[Value])

Source_final = if Source[HasError] then File.Contents(filestring2) else File.Contents(filestring1)

And then apply the csv transformations


Edit2: You could also simplify step1 as :
try Binary.Length(File.Contents(filestring1))

[Artur_]

It works, thank you very much! 🙂