cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jnickell
Resolver I
Resolver I

Issues with using a parameter in web.content url in dataflow

This seems to be something others have struggled with and I've tried a few different ways, but when I attempt to create a function, and pass it as a parameter used in getting a specific web resource I receive the message. "One or more entities references a dynamic data source."  This happens when I try to save the dataflow, even with "Enable Load" unchecked and only having declared the function, not actually using it yet.

The query "compiles"? fine in the editor, but I'm unable to save the dataflow if this query is included.  Is this a license limitation?, I have PowerBI pro.

Calling out the area where I'm having issues.

ReportResult = Web.Contents(ReportURL,[Headers=ReportHeader]),
 
I've tried using "ReportID" as the actual ID value that the URL needs to include, and also just as a value to switch between two fully declared URLs.  Neither works.
 

 

let
  Query = (ReportID as text) => let
        /// Get a Auth Token to connect with
        LoginAuthURL = "https://fqdn/ta/rest/v1/login",
           
        PostBody = [
            credentials = [
                company = "xxxx",
                username = "xxx",
                password = "xxxxx"
            ]
        ],
        Header = [#"Content-type"="application/json",
                    #"Api-Key" = "dt8udy2jzhyzn6v4qpd16ykpz6ukc698"                
                  ],
        out = Json.Document( Web.Contents(LoginAuthURL, [Content=Json.FromValue(PostBody),Headers=Header])),
        #"Converted to Table" = Record.ToTable(out),
        #"Filtered Rows" = Table.SelectRows(#"Converted to Table", each ([Name] = "token")),
        Token = #"Filtered Rows"{0}[Value], 
    
        /// Pull Report from System
        /// ReportURL1
        ReportURL1 = "https://fqdn/ta/rest/v1/report/saved/123456",
        /// ReportURL2
        ReportURL2 = "https://fqdn/ta/rest/v1/report/saved/654321",
        /// workaround for not being able to declare the ReportID as a parameter
        ReportURL = if ReportID = 1 then 
                        ReportURL1 
                    else ReportURL2,

        ReportHeader = [
            #"Content-type"="application/json",
            #"Api-Key" = "jakldjafkljsdklfajsdkljklasdjklas",
            #"Authentication" = "Bearer " & Token
        ],
        ///// These won't "save"
        ///ReportResult = Web.Contents("https://fqdn/ta/rest/v1/report/saved/" & ReportID,[Headers=ReportHeader]),
        ///ReportResult = Web.Contents(ReportURL,[Headers=ReportHeader]),

        /////  This "will save"
        ReportResult = Web.Contents(ReportURL1,[Headers=ReportHeader]),
        ReportOutput = Csv.Document(ReportResult),
        #"Promoted Headers" = Table.PromoteHeaders(ReportOutput, [PromoteAllScalars=true])
    in
        #"Promoted Headers"
in
  Query

 

1 ACCEPTED SOLUTION
jnickell
Resolver I
Resolver I

After some more digging, I think I found something that works.  Using, RelativePath the function attribute.

I updated the query to have ReportQueryURL be the full path up to the "ID" value I needed to parameterize.  

 

ReportResult = Web.Contents(ReportQueryURL,
          [RelativePath=ReportID,
          Headers=ReportHeader]),
        ReportOutput = Csv.Document(ReportResult),

 

I'm able to save the dataflow, with the function, and my first 'test' of the function is working as well.  

View solution in original post

3 REPLIES 3
jnickell
Resolver I
Resolver I

After some more digging, I think I found something that works.  Using, RelativePath the function attribute.

I updated the query to have ReportQueryURL be the full path up to the "ID" value I needed to parameterize.  

 

ReportResult = Web.Contents(ReportQueryURL,
          [RelativePath=ReportID,
          Headers=ReportHeader]),
        ReportOutput = Csv.Document(ReportResult),

 

I'm able to save the dataflow, with the function, and my first 'test' of the function is working as well.  

Hi @jnickell

 

So has the issue  been solved now?

 

Best Regards,
Kelly
 
Did I answer your question? Mark my post as a solution!

Yes my issue seems to be resolved now.  

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors