Forum Discussion
MyThumbsClick
2 years agoHelper II
Use existing table column to loop through each id and create a new table with API
Hi All Im working with the Freshservice API (Ticket platform) and have had good success pulling in ticket and associated tables/data into Power BI. I have used this link to get around pagination iss...
lbendlin
2 years agoSuper User
you already know the ticket ID as you use that for the call. Include the column when you flatten out the received JSON.
- MyThumbsClick2 years agoHelper II
Im struggling to create a function and then loop through the IDs of an exisiting table to feed into the API call.
This is the function (which works fine):
//GetTicketTimeEntries (TicketID as number) => let Source = Json.Document(Web.Contents(FreshserviceBaseURL, [RelativePath="/tickets/" & Number.ToText(TicketID) & "/time_entries", Headers=[Authorization=FreshserviceAuthHeader]])) in Sourceand this is the table query:
//AllTimeEntries let Records = List.Generate(()=> [Source = GetTicketTimeEntries(1), id=1], each List.Count([Source][time_entries]) > 0, each [Source = TicketID ([id]+1), TicketID=[id] +1], each [Source]) in RecordsI cant figure out how to insert the source to loop through being the TicketIDs of the All Tickets table, id column.
- lbendlin2 years agoSuper User
Not sure what you need the second query for. Can't you just use the JSON from the API call directly? Or is there a typo in the
each [Source = TicketID ([id]+1), TicketID=[id] +1],line?
- MyThumbsClick2 years agoHelper II
Yeah, im totally confused to be honest. Are you saying use the ListGenerate function in the first query?