Looping API Requests: references other queries or steps, so it may not directly access a data source

[laurie_oa]

In PowerBI desktop I've managed to successfully create a function that loops an API request having been passed some data from a table. It has to be done this way because the endpoint is restrctive, it returns one small chunk of data per request. This works pretty well in the desktop but when I publish the dataset I get this: 

references other queries or steps, so it may not directly access a data source

Which I think I understand but haven't been able to circumvent. 

I've turned off privacy options for that dataset so it isn't that. I've tried "Staging" all the data and previous queries in separate tables before running the loop function but I'm still getting the same result.

The loop function:

let timesheetFromId = (input as table) =>
    let
        Source = Table.AddColumn(input,"TimeSheets", each GetJson("api/v2/Organisation/" & slug & "/Timesheet/Week/?contactId=" & Number.ToText([UserID]) & "&dateAsInt=" & Date.ToText([Week],"yyyyMMdd")))      
    in
        Source
in timesheetFromId

The GetJson function that this calls:

(usedRelativeUrl) =>
        let  options = [Headers = [#"IGNORE_ME_Content-TypeTT"="application/json",
            #"access-token"=accessToken],
            RelativePath=usedRelativeUrl
            ],
            RawData = Web.Contents(environmentUrl, options),
            Json    = Json.Document(RawData)
        in  Json

This uses parameters with environment info about the API.

The table resulting from calling the timesheetFromId function and passing it a table with UserID and Week:

Looking through the table it's returning the correct data, working well (but slowly), however publishing the dataset returns this:

Am I going about this the wrong way? Should I be collapsing all queries into a single function? Or am I not staging things correctly? 

[blopez11]

What does the function GetTimeSheetByDateID look like?

It would be useful to see everything that leads up to the GetTimeSheetByDateID function, even if it is just the begin/end of functions or staging queries to hide your IP

Typically when I run into this I have to encapsulate the data sources behind functions, but from what I can see so far, it looks like you are doing that

[laurie_oa]

Hey Blopez11, 

Thanks for your help. 

Apologies for the confusion, GetDateByTimesheetID and timeSheetFromId are the same thing - bad naming on my part. In terms of the full breakdown: 

Function that generates the dates I requireThe loop function itselfThis takes an API URL and access tokens and makes the call

Splicing Week/User data to make a

UserWeekStagedDate - Just combines the dates with the IDs and expands the dates out. 

Bringing everything together

It's a little bit convoluted exactly because I've tried to break everything up into functions. Is it possible the date generation part needs to be split out into its own function?

I still don't see how it's considered mixing data sources at this point, or am I just getting the "Catch-all can't cope" error?

Cheers,

laurie_oa

[blopez11]

I do agree with you on that pulling data from the same sources shouldn't be considered mixing

I have run into that with web services pulling from the same root url

Anyway, I think you may need to encapsulate the UserWeekStaged into a function, and quite possibly TimesheetWeeks

Web services are quite finicy, in that what works in the desktop doesn't always work in the service