Power Query - Problem refreshing a report with dynamic data source

[omargazzar]

Hello all,

I created a PBI report that reads the content of a text file, which has a single URL link that is simply a link to download a csv file. I then managed to create a function that would extract the contents of that excel file, then I invoked this function to add the table content to my main table (this was my reference Use Power BI Web From Example over multiple URLs). This report refreshes normally on Power BI desktop, but as soon as I publish the report on service I get that dynamic data source error message.

I read several articles talking about using the RelativePath function to bypass this problem, so I attempted at adding that RelativePath function next to the parameter used to hold the content of the URL within the function. Then got "Expression.Error: We cannot convert a value of type Record to type Text" message when I tried to add an Invoked Custom Function step within my main table query.

Below is my function query, my parameter is called "Para_LinkUrl" and it is just a text parameter containing a sample url link

--------------------------------

= (Para_LinkUrl as text) => let
Source = Csv.Document(Web.Contents([RelativePath = Para_LinkUrl]),[Delimiter=",", Columns=10, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])
in
#"Promoted Headers"

-------------------------------

Just FYI, the URL link text file is a result of a Power Automation flow that parses an email to extract a link to download the csv file.

Any help would be greatly appreciated.

Thanks,

[v-saisrao-msft]

Hi @omargazzar,
I wanted to check if you had the opportunity to review the information provided by @lbendlin. Please feel free to contact us if you have any further questions. If the  response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[omargazzar]

Hi @v-saisrao-msft, I am still trying to figure out how to inline my function. I read the provided documentation, but still a little bit confused given that the function gets invoked from another table.

[lbendlin]

given that the function gets invoked from another table

that's not what "inline"  means. Don't do that.

[omargazzar]

Hey @lbendlin, thanks for getting back to me. Do you reckon there is a solution to this problem? The function is meant to open up a link found within a parameter. I added an invoke custom function in my main query that calls that function and passes a specific value through the parameter used in the function.

[lbendlin]

I added an invoke custom function

Instead of doing that, inline the function.

[omargazzar]

Hey @lbendlin, can you please advise how to do that?
I tried inlining the call function within my main query as shown below and received the following error message.

from the main query
= Table.AddColumn(#"Removed Columns1", "CallingFunction", (callFunctionInline) => FunctionInvoker([Original_URL_Link.2.2]))

Error message for the added invoked custom function

Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?

Any help would be appreciated.

[v-saisrao-msft]

Hi @omargazzar,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[omargazzar]

Hello @v-saisrao-msft, 

I did go through the information provided, and I did follow this one before to deal with the dynamic queries issue that I had. The problem now is I'm having issues inlining my function based on @lbendlin feedback.

from the main query
= Table.AddColumn(#"Removed Columns1", "CallingFunction", (callFunctionInline) => FunctionInvoker([Original_URL_Link.2.2]))

Error message for the added invoked custom function

Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?

My function is shown below
= (Parameter1 as text) => let
Source = Csv.Document(Web.Contents("STATIC_URL",[RelativePath = Parameter1]),[Delimiter=",", Columns=10, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])
in
#"Promoted Headers"

[lbendlin]

= Table.AddColumn(#"Removed Columns1", "CallingFunction", each Table.PromoteHeaders(Csv.Document(Web.Contents("STATIC_URL",[RelativePath = [Parameter1]]),[Delimiter=",",Encoding=65001, QuoteStyle=QuoteStyle.Csv]), [PromoteAllScalars=true]))

[omargazzar]

Thanks @lbendlin, followed that one, managed to refresh it on Power BI desktop. Published it to Service, and got the following error message while refreshing via service. Looping back to the partitioning issue that you have shared with me before.

[Unable to combine data] Section1/Main_Query/Expanded CallingFunction is accessing data sources that have privacy levels which cannot be used together. Please rebuild this data combination.. The exception was raised by the IDataReader interface. Please review the error message and provider documentation for further information and corrective action. Table: Main_Query.

[v-saisrao-msft]

Hi @omargazzar,

We haven’t heard back from you regarding your issue. If it has been resolved, please mark the helpful response as the solution and give a ‘Kudos’ to assist others. If you still need support, let us know.

Thank you.

[omargazzar · Accepted solution]

Hey @v-saisrao-msft & @lbendlin, thank you for all your help. I did ignore the privacy level on the current file, refresh process works normally on Power BI Desktop, but as soon as I upload to Service and try to refresh, I get the same error message on Power BI Service. 

Happy for you guys to close this thread. It looks like this one just simply can't be resolved with the current restrictions in my organization.

I'll try exploring more on Power Automation to download the file for me.

[v-saisrao-msft]

Hi @omargazzar,

Thanks for the update! It sounds like you've done a thorough job troubleshooting within the limits of your environment. Organizational restrictions can definitely make certain solutions more challenging.

Exploring Power Automate sounds like a good direction hopefully that gives you the flexibility you need.

Thank you.

[v-saisrao-msft]

Hi @omargazzar,
I wanted to check if you had the opportunity to review the information provided by @lbendlin . Please feel free to contact us if you have any further questions. If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[lbendlin]

One option (not recommended) is to "ignore privacy levels for this file"

[v-saisrao-msft]

Hi @omargazzar,
You might find this helpful for resolving your issue:
Solved: Dynamic data sources aren't refreshed in the Power... - Microsoft Fabric Community

Thank you.

[omargazzar]

Thanks @lbendlin, I followed your advise. I added the static base url, and the report refreshed on Power BI Desktop successfully.

Published the report to Power BI Service, and got a different error message when trying to refresh from service. I tried skipping the connection test, even set them with the same privacy level settings, but still no luck.

• Data source error[Unable to combine data] Section1/Query_Test/Expanded functionInvoker is accessing data sources that have privacy levels which cannot be used together. Please rebuild this data combination.. The exception was raised by the IDataReader interface. Please review the error message and provider documentation for further information and corrective action. Table: Query_Test.

The Query_Test test table contains the text file value that gets passed into the parameter Para_LinkUrl, all I've doing there is Invoke Custom function to pass the value to the function to retrieve the table contents.

---------------------------------
= (Para_LinkUrl as text) => let
Source = Csv.Document(Web.Contents("BASE_URL",[RelativePath = Para_LinkUrl]),[Delimiter=",", Columns=10, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])
in
#"Promoted Headers"

---------------------------------

The full URL does not get blocked by itself when I try to refresh that table in Power BI Service (ie no firewalls blocking anything there)

Thank you for your help

[lbendlin]

keep everything in the same partition - inline your function.

Behind the scenes of the Data Privacy Firewall - Power Query | Microsoft Learn

[lbendlin]

Your code is missing the (static) base URL.