Source file in shared OneDrive folder depends on local drive

[DRSteele]

Can someone please help me?

I (DRS) have a file stored in a shared folder in OneDrive that contains some queries. The sources for the queries are CSV files stored in that folder's sub-folder in OneDrive and are updated regularly. So the source looks like this:

C:\Users\DRS\OneDrive\DRS_JRS\Investments\JRS_StockData\UntitledWatchList-stocksAndEtfs.csv

When I attempt to open the file using JRS's computer, I get "DataSource.Error: Could not find a part of the path...."

JRS is forced to alter the queries to C:\Users\JRS\etc to get the queries to function; this is unworkable. I hope someone can provide a solution or suggestions.

Thanks in advance.

 

[ImkeF]

Try this: How to auto-parametrize your Power Query queries – The BIccountant
Please tell if you need help adjusting it to your specific use case.

 

[DRSteele]

Thanks for your prompt reply. I read through and attempted the code in Allington's blog. I've struggled heroically but I'm clearly out of my depth. Can you help me more please?

1) I created a table (Table3) in my workbook (the one that gets shared).

	F	G	H
4	type	user	value
5	CurrentUser		DRS
6	DataStocks	DRS	C:\Users\DRS\SkyDrive\DRS_JRS\Investments\JRS_StockData
7	DataStocks	JRS	C:\Users\JRS\OneDrive\DRS_JRS\Investments\JRS_StockData

<tbody>
</tbody>

query​

2) I then created the Function to invoke for the Stocks file. I called it ParametersStocks:

let
ParametersStocks = (myParameter) =>

let
Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
MyUser = Table.SelectRows(Source, each ([user] = null)),
MyUserValue=Record.Field(MyUser{0},"value"),
#"Filtered Rows" = Table.SelectRows(Source, each ([user] = MyUserValue) and ([type] =myParameter)),
MyReturnedParameter=Record.Field(#"Filtered Rows"{0},"value")
in
MyReturnedParameter,
#"Invoked FunctionParametersStocks" = ParametersStocks("DataStocks")
in
#"Invoked FunctionParametersStocks"


3) So far so good, I think. I then altered the query for Stocks to this, and it fails. The error is "Expression.Error: We cannot convert the value "C:\Users\DRS\SkyDriv..." to type Function". [Here I omitted the remainder of the query that deals with all the editing.]

let
//Source = Csv.Document(File.Contents("C:\Users\DRS\SkyDrive\DRS_JRS\Investments\JRS_StockData\UntitledWatchList-stocksAndEtfs.csv"),[Delimiter=",",Encoding=1252]),
myPath=ParametersStocks("DataStocks"),
Source = Excel.Workbook(File.Contents(myPath&"\UntitledWatchList-stocksAndEtfs.csv"), [Delimiter=",",Encoding=1252]),
#"Promoted Headers" = Table.PromoteHeaders(Source),

 

[ImkeF]

Looks alright from here apart one thing: You still have to open your source as a Csv.Document instead of an Excel.Workbook.

So this is how you would call the function instead:

Source = Csv.Document(File.Contents(myPath&"\UntitledWatchList-stocksAndEtfs.csv"), [Delimiter=",",Encoding=1252]),

 

[DRSteele]

Thanks again ImkeF. I altered the query to reflect that change to Csv but I get the same error. When I click on the gear symbol beside myPath in the Applied Steps area it tells me:

Error Message:


Value cannot be null.
Parameter name: source

 

[DRSteele]

ImkeF, I figured it out but smashing around in the Query Editor. Either I misread Allington's blog or it wasn't clear.

I wasn't supposed to invoke the function by passing a parameter through it; I was supposed to let the query pass the parameter through the function! So I Close&Loaded the function ParametersStocks without invoking. Now everything seems in order.

See? Even a blind squirrel finds an acorn every once in a while.

 

[DRSteele]

ImkeF, I figured it out but smashing around in the Query Editor. Either I misread Allington's blog or it wasn't clear.

I let Arlington know about this and he altered his blog posting to clarify the need to leave the function un-invoked.