Cannot refresh in PBI service - "Query contains unsupported function. Function name: Folder.Files"

[kotelo]

Hi everyone, first time posting here. So, here's my issue.

I want to schedule a refresh in PBI service but when I try I get this error:

I have some rookie-level transformations for getting the most recent document and with that I also use parameters for the source files' path, I do this for TXT and XLSX files. I also use parameters for DB connection strings.

Is there any solution for this? Is the problem due to using "Folder.Files" to load the data? Are Parameters the issue? Maybe both? If so, should I just avoid parameters completely for this? Another thing: I'm also using a query to load some user-defined data (txt) into the model, like Period to load (i.e 201906) and I use that to identify the data that I must load into the model among the jungle of directories the user has. And I really need this one...

Sorry for dragging this a bit but I don't want to miss any details. So, is there a solution for this "parameterized load paths"/"Folder.Files" issue? Or should I just avoid them?

Thanks in advance!

[kotelo]

So, any ideas? Or it's just not possible?

[foxb3692]

Hello @kotelo,

I'm wondering if you were ever able to resolve this issue?

In my testing, it appears to me that the function that is at issue is not Folder.Files, but really Text.From.

I believe this because I am able to successfully configure the On Premises Data Gate and schedule Refreshes for both hard-coded Folder.Files paths, and a Folder.Files that use a text query with a file path prefix, i.e. Folder.Files(CompanyFilePath & "fileName.txt"), but receive the same error message that you receive when I add one more distinguishing characteristic, Folder.Files(CompanyFilePath & Text.From(CompanyID) & " fileName.txt")

-Burton

[v-xicai]

Hi  @kotelo ,

Does that make sense? If so, kindly mark my answer as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.

Best regards

Amy

[kotelo]

Hi Amy, and sorry for the late reply, but your solutions didn't work either and I still can't "fix" this.

Let me add some details on what I'm doing:

1. I have a TXT file (located in a shared folder, ie \\data\MainFolder\Config\Period.txt) where the user enters the Period (ie 201908) and I save that as text in a query

ie. Source = Text.From(Table.FirstValue(Table.FromColumns({Lines.FromBinary(File.Contents( parameterRootPath & "Config\" & "Period.txt"), null, null, 1252)})))

2. Based on that data, I get month name, year and of course, period.

3. I save that data into separate queries to later on use them to create file path

ie. Source= Folder.Files( parameterRootPath & "Area1\" & queryYear & "\" & queryMonthName & "\")

And that's my issue, Folder.Files doesn't work in service...

I've tried turning the formatted path into a parameter (the same as in step3) and deliver that to Folder.Files, but it doesn't work either. I'm really scratching my head with this, and I really don't understand why it works on Desktop but not in Service.

[v-xicai]

Hi @kotelo ,

There are several points you can considerate:

1.You may refer to the link Import Multiple Files from a Folder with Power Query #1.

2.If your dataset contains only the on premise data source, then it needs to use the gateway and configure schedule refresh, see more: :On-premises data gateway , Configure scheduled refresh .

3.If your dataset contains not only on premise data source but also the cloud data source, then it needs to use the gateway and configure schedule refresh . Note that you also need to select Allow user's cloud data sources to refresh through this gateway cluster option Under Gateway Cluster Settings. See: Merge or append on-premises and cloud data sources.

4.You can try to upgrade the on premise gateway to latest version.

5.Maybe you can try to connect to the multiple data sources separately.

You may resolve the issue via the troubleshooting link :https://docs.microsoft.com/en-us/power-bi/refresh-troubleshooting-refresh-scenarios .

If you still have this issue for Power BI, you'd better create a support ticket in Power BI Support to get further help.

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.