Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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))
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
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?
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))
It works, thank you very much! 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
101 | |
50 | |
19 | |
12 | |
11 |