Reading csv PowerQuery [Source = try ... otherwise ... ] fails, paths are valid

[JackDanIce]

Hi,

I'm using my PC and my laptop, both connected to a personal OneDrive account. Microsoft 365 (personal version, non scripts anyway)

I have code like:

let
    Source = try
                Csv.Document(File.Contents("C:\Users\JackDanOneDrive\Work\Karrus Capital\Earthi\Data\Copper Futures Historical Data.csv"), [Delimiter=",", Columns=7, Encoding=65001, QuoteStyle=QuoteStyle.None])
            otherwise
                Csv.Document(File.Contents("C:\Users\Admin\OneDrive\Work\Karrus Capital\Earthi\Data\Copper Futures Historical Data.csv"),[Delimiter=",", Columns=7, Encoding=65001, QuoteStyle=QuoteStyle.None]),

Code is trying to read data from a personal cloud folder, which is mapped to each machine as paths imply, difference 1 folder name JackDan vs Admin.

When I open this file on my laptop, the csv file loads in fine.
When I open it on my pc, the csv file does not load in, nor does the laptop equivalent.

If I switch positions of the paths in the statement, close, then open on each machine, I get the opposite behaviour, csv data loads file into PC, but not laptop

let
    Source = try
                Csv.Document(File.Contents("C:\Users\\OneDrive\Work\Data\Copper Futures Historical Data.csv"),[Delimiter=",", Columns=7, Encoding=65001, QuoteStyle=QuoteStyle.None])
            otherwise
                Csv.Document(File.Contents("C:\Users\JackDan[/COLOR]\OneDrive\Work\Data\Copper Futures Historical Data.csv"), [Delimiter=",", Columns=7, Encoding=65001, QuoteStyle=QuoteStyle.None]) ,

First reaction is I have syntax wrong, but I am not getting any syntax errors
Second reaction, I have the paths wrong, but if I use Data->From Text/CSV as separate queries from both machines, the paths in the generated code are identical.
Third reaction, if try fails, otherwise should catch error and continue but I'm finding data is only read in from the machine I'm at, IF the path to the needed CSV file is after try, not otherwise.

So far workaround has been two separate queries
Then a 3rd query that is like

let
    Source = try laptopcsv otherwise pccsv,

So far this set up is the only way I can easily open the Excel file and read in the data as needed.

Any ideas what I'm doing wrong or what's causing this behaviour and how to resolve?

TIA,
Jack

 

[jdellasala]

let
Source = try
Csv.Document(File.Contents("C:\Users\\OneDrive\Work\Data\Copper Futures Historical Data.csv"),[Delimiter=",", Columns=7, Encoding=65001, QuoteStyle=QuoteStyle.None])
otherwise
Csv.Document(File.Contents("C:\Users\JackDan[/COLOR]\OneDrive\Work\Data\Copper Futures Historical Data.csv"), [Delimiter=",", Columns=7, Encoding=65001, QuoteStyle=QuoteStyle.None]) ,

Unless you were intentionally masking the paths, neither of them will work but have no syntax problems!

 

[JackDanIce]

Hi there,
Thanks for replying, apologies delayed reply. Yes, I was attempting to mask paths(!) but the basic folder pattern is the same after the User name for both, e.g.

let
    Source = try
                Csv.Document(File.Contents("C:\Users\jackdanice\OneDrive\Work\Earthi\Data\Copper Futures Historical Data.csv"), [Delimiter=",", Columns=7, Encoding=65001, QuoteStyle=QuoteStyle.None])
            otherwise
                Csv.Document(File.Contents("C:\Users\Admin\OneDrive\Work\Earthi\Data\Copper Futures Historical Data.csv"),[Delimiter=",", Columns=7, Encoding=65001, QuoteStyle=QuoteStyle.None]),

The two paths are nearly identical, except the username inbetween "\Users\" and "\OneDrive" ( bit like =Substitute("C\Users\ABC\file.csv", "ABC", "DEF") )

If I run the code from PC, the csv data only imports if the PC path is before otherwise.
If try from laptop, the data only imports if the laptop path is before otherwise.

Limited data to test with but the experience so far is making me wonder if it's a security or some issue that when i test for a path it can't find depending on machine used, it isn't trying the otherwise part either

(Files are 122Kb and 91KB in size respectively, copied and pasted into the file, inserted a table, pulls into PQ fine - surprise! As tempfix, it'll do)