Forum Discussion

webportal's avatar
webportal
Impactful Individual
9 years ago

Write M code to run multiple queries and the append them all into a single table

Hello,

 

I have the following query to fetch data from Insightly API:

 

let
    Source = Json.Document(Web.Contents("https://api.insight.ly/v2.2/Tasks?skip=7000&top=7500", [Headers=[Authorization="Basic ***", ContentType="application/json"]])),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded {0}" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"TASK_ID", "TITLE", "CATEGORY_ID", "DUE_DATE", "COMPLETED_DATE_UTC", "PUBLICLY_VISIBLE", "COMPLETED", "PROJECT_ID", "OPPORTUNITY_ID", "MILESTONE_ID", "PIPELINE_ID", "STAGE_ID", "DETAILS", "STATUS", "PRIORITY", "PERCENT_COMPLETE", "START_DATE", "ASSIGNED_BY_USER_ID", "PARENT_TASK_ID", "OWNER_VISIBLE", "RESPONSIBLE_USER_ID", "ASSIGNED_TEAM_ID", "ASSIGNED_DATE_UTC", "OWNER_USER_ID", "DATE_CREATED_UTC", "DATE_UPDATED_UTC", "REMINDER_DATE_UTC", "REMINDER_SENT", "RECURRENCE", "TASKLINKS", "CAN_EDIT", "CAN_DELETE"}, {"TASK_ID", "TITLE", "CATEGORY_ID", "DUE_DATE", "COMPLETED_DATE_UTC", "PUBLICLY_VISIBLE", "COMPLETED", "PROJECT_ID", "OPPORTUNITY_ID", "MILESTONE_ID", "PIPELINE_ID", "STAGE_ID", "DETAILS", "STATUS", "PRIORITY", "PERCENT_COMPLETE", "START_DATE", "ASSIGNED_BY_USER_ID", "PARENT_TASK_ID", "OWNER_VISIBLE", "RESPONSIBLE_USER_ID", "ASSIGNED_TEAM_ID", "ASSIGNED_DATE_UTC", "OWNER_USER_ID", "DATE_CREATED_UTC", "DATE_UPDATED_UTC", "REMINDER_DATE_UTC", "REMINDER_SENT", "RECURRENCE", "TASKLINKS", "CAN_EDIT", "CAN_DELETE"}) 
in
    #"Expanded {0}"

Since the API limits the number of rows to 500, I need to create multiple queries with 500 rows each, and then append them in Power BI.

 

Is there a way to rewrite the code above to do this automatically, maybe using the the parameters skip and top to run several queries?

 

Thanks for helping!

Need Help

14 Replies

  • ImkeF's avatar
    ImkeF
    Community Champion
    9 years ago

    If you know beforehand how many chunks you need, I'd prefer to create a table with 2 columns: "From" and "To" and pass these to figures as argument to your function in a Table.AddColumn-step. You'd just have to expand that column then.

     

    A more advanced technique is the pagination via List.Generate which is necessary if you don't know the number beforehand or need to retrieve a value from the current step to trigger the next step. It is beautifully shown here: https://www.youtube.com/watch?v=vhr4w5G8bRA

     

    Please let me know if you need any help for one of the mentioned techniques.

  • Eric_Zhang's avatar
    Eric_Zhang
    Microsoft Employee
    9 years ago

    webportal

    ImkeF‘s approach is really cool, however I'm afraid that might only work in Power BI desktop. When publish to web, such dataset can't be refreshed.

     

     

    For those REST APIs that don't have a built-in connector in Power BI, I still suggest developing an external application calling the APIs and saving data in one file or database, then connect from Power BI to the file or DB.

    • webportal's avatar
      webportal
      Impactful Individual
      9 years ago

      ImkeF and Eric_Zhang

      Thanks both for your help.

      I think the proposed solution is a bit complicated to someone just starting with M, since I don't know in advance how many rows will be imported (the database is growing).

      So I just patiently downloaded all the historical data into a spreadsheet, since it won't be updated anyway, and got the last 500 rows in a query via API in another table.

      I then merged the two tables into a new one.

      In Power BI Desktop, I can see the new data is refreshed (by clicking the refresh button), but haven't tested yet in Power BI Service to check if it also refreshes with the Data Gateway.

      This solution seems to work, although it requires more maintenance. 

      I'm still interested in a more automated solution, so if you have any files you can share with samples, I'd appreciate.

      Regards