Function to get dynamic file names not working with RelativePath

[echoniner]

Hi!

So, I have a query that's working with a function. Here it is:

(file as text) =>

let
Source = Json.Document(File.Contents("E:\FTP\Client\Data\"&(file))),

That's working fine, but it won't refresh on Power BI Online, because of that custom function.

I know that there is a workaround to the Refresh if I use a Relative Path.

So, I setup the query with the function to use a Relative Path instead. Here's what I set up:

(file as text) =>

let

Source = Json.Document(File.Contents("E:\", [RelativePath="FTP\Client\Data\"& file &""])),

The function seems to work, but when I reference it in the other query, I get an error message

"An error occurred in the ‘’ query. Expression.Error: 2 arguments were passed to a function which expects 1."

Any thoughts or ideas are very appreciated!

Thanks.

[v-juanli-msft · Accepted solution]

Hi @echoniner 

If you import data into Power BI, It should support for refresh in Power BI Service.

Edit parameter settings in the Power BI service

You could create a query parameter under Edit queries->Home->Manage parameter->New Parameter

Power BI Introduction: Working with Parameters in Power BI Desktop —Part 4

Then use this parameter ((file parameter) in the Source code.

Reference:

https://gqbi.wordpress.com/2016/11/23/power-bi-using-parameters-for-flat-file-csv-excel-sources/

https://www.excelguru.ca/blog/2018/05/30/creating-dynamic-parameters-in-power-query/

As for use a Relative Path, I hope this link will be helpful.

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

I was able to get this working.

Thanks again for your response, @v-juanli-msft 

[v-juanli-msft]

Hi @Anonymous 

Since it is sloved, could you kindly share the solution to more people here and accept it as a solution to close this case?

Best Regards

Maggie

[echoniner]

Hi @v-juanli-msft 

So, I was able to login to my account that started this message.

Just wanted to post again in case something got weird with using the other account.

So, thank you for this info! I went through the articles, and it seems close to what I'm looking for, but the parameters are talking about referencing dynamic folders, and I need to reference dynamic files names, and can't seem to figure it out.

I should explain what the full scenario is.

I'm just trying to connect to a folder that has multiple files in it. That would be easy if it was .csv files, but it's a folder with .json files, and Power BI can't connect to a folder with .json files, only a single .json file.

There's an article I found that has a workaround for that, which basically is connecting to one .json file, making that connection a function with a dynamic file name. Then, create a new connection to the folder, and then add a custom function column that references the function I created.

Here's the article link, which will probably make a lot more sense than what I just said: http://blog.pragmaticworks.com/power-bi-problem-design-solution-folder-source-option-for-json-files

So, I totally have that working. But the problem (and it's a big one) is that the data won't automatically refresh on Power BI Online. It has something to do with PBI Online not recognizing the custom function...

Hopefully that makes sense?

Thanks,

Jonathan

[echoniner]

So, thank you for this info! I went through the articles, and it seems close to what I'm looking for, but the parameters are talking about referencing dynamic folders, and I need to reference dynamic files names, and can't seem to figure it out.

I should explain what the full scenario is.

I'm just trying to connect to a folder that has multiple files in it. That would be easy if it was .csv files, but it's a folder with .json files, and Power BI can't connect to a folder with .json files, only a single .json file.

There's an article I found that has a workaround for that, which basically is connecting to one .json file, making that connection a function with a dynamic file name. Then, create a new connection to the folder, and then add a custom function column that references the function I created.

Here's the article link, which will probably make a lot more sense than what I just said: http://blog.pragmaticworks.com/power-bi-problem-design-solution-folder-source-option-for-json-files

So, I totally have that working. But the problem (and it's a big one) is that the data won't automatically refresh on Power BI Online. It has something to do with PBI Online not recognizing the custom function...

Hopefully that makes sense?

Thanks,

Jonathan

[Anonymous]

Hi @v-juanli-msft!

I've been having problems with my account (echoniner), and was able to sign in with my old account. Sorry for the confusion!

So, thank you for this info! I went through the articles, and it seems close to what I'm looking for, but the parameters are talking about referencing dynamic folders, and I need to reference dynamic files names, and can't seem to figure it out.

I should explain what the full scenario is.

I'm just trying to connect to a folder that has multiple files in it. That would be easy if it was .csv files, but it's a folder with .json files, and Power BI can't connect to a folder with .json files, only a single .json file.

There's an article I found that has a workaround for that, which basically is connecting to one .json file, making that connection a function with a dynamic file name. Then, create a new connection to the folder, and then add a custom function column that references the function I created.

Here's the article link, which will probably make a lot more sense than what I just said: http://blog.pragmaticworks.com/power-bi-problem-design-solution-folder-source-option-for-json-files

So, I totally have that working. But the problem (and it's a big one) is that the data won't automatically refresh on Power BI Online. It has something to do with PBI Online not recognizing the custom function...

Hopefully that makes sense?