Forum Discussion

omargazzar's avatar
omargazzar
Frequent Visitor
1 year ago
Solved

Power Query - Problem refreshing a report with dynamic data source

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,

Need Help
  • omargazzar's avatar
    omargazzar
    1 year ago

    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.

19 Replies

  • v-saisrao-msft's avatar
    v-saisrao-msft
    Community Support
    1 year ago

    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's avatar
      omargazzar
      Frequent Visitor
      1 year ago

      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's avatar
        lbendlin
        Super User
        1 year ago
        given that the function gets invoked from another table

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

  • omargazzar's avatar
    omargazzar
    Frequent Visitor
    1 year ago

    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