Circular Dependency issue with Power Query Function

[alan2468]

I have a power query that calls a function to get PowerBI Activity with an API Call.  The function keeps calling itself combining the data until the continuation token is null.  This works fine in PowerBI Desktop.  But in Power BI Dataflow, I get circular dependency error.

This is the power query.  Does anyone know how to get dataflow to not error with the circular dependency?
The Function Name is:  GET Activity

(optional startDate as text, optional endDate as text, optional continuationToken as text, optional loop as number, optional data as list) =>

let

    Source = Json.Document(Web.Contents("https://api.powerbi.com/v1.0/myorg/",

      [

          RelativePath = if loop = 0

            then "admin/activityevents?startDateTime=" & startDate & "&endDateTime=" & endDate

            else "admin/activityevents?continuationToken='" & continuationToken & "'",

          Headers=[Authorization="Bearer " & GETrefreshToken() & " "]

      ])),

      token = Source[continuationToken],

      currentData = Source[activityEventEntities],

      appendedDate =

        if currentData is null and data is null then {}

        else if data is null then List.Combine({{}, currentData})

        else List.Combine({data, currentData}),

        loopNum = loop + 1,

        output =

            if token is null

            then

                if appendedDate is null then {1,2,3} else appendedDate

                else @#"GET Activity"("", "", token, loopNum, appendedDate)

in

    output

[rubayatyasmin · Accepted solution]

Hi, @alan2468 

Power Query in Power BI Desktop and Power BI Dataflow have some differences in how they process queries. Dataflows are designed to discourage or prevent circular references, which can cause sync issues in a cloud environment. In contrast, Power BI Desktop is more lenient with such dependencies because the processing is local.

Instead of using recursion, rework the logic to be iterative, you can use while loop to process pages of data until the continuation token is null, appending results after each call.  If possible, pre-load the data to an intermediate storage (like Azure Blob Storage) then use DF to the read the data. If the data size is manageable, you might retrieve all the data in one call, then use schedule refresh.