Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
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
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:
UserWeekStagedDate - Just combines the dates with the IDs and expands the dates out.
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.