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

[webportal]

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!

[Eric_Zhang]

@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]

@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

[danextian]

Hi @webportal

Have you tried creating a custom function out of your existing query?

[ImkeF]

Check out this thread for multiple approaches: http://community.powerbi.com/t5/Desktop/how-to-create-a-query-that-paginates/m-p/224618#M99892

I believe there are also posts with links to files in it.

This is a very good step-by-step introduction: https://datachant.com/2016/06/27/cursor-based-pagination-power-query/

[webportal]

@ImkeF you rock, baby!

[webportal]

This is what I''m trying now:

let

Function = (Start as text, Finish as text) =>

let
 Source = Json.Document(Web.Contents("https://api.insight.ly/v2.2/Organisations?skip="&Start&"&top="&Finish&"&", [Headers=[Authorization="Basic *****", ContentType="application/json"]])),
 data = Source[data],
 #"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
 #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"ORGANISATION_ID", "ORGANISATION_NAME", "BACKGROUND", "IMAGE_URL", "OWNER_USER_ID", "DATE_CREATED_UTC", "DATE_UPDATED_UTC", "VISIBLE_TO", "VISIBLE_TEAM_ID", "VISIBLE_USER_IDS", "CUSTOMFIELDS", "ADDRESSES", "CONTACTINFOS", "DATES", "TAGS", "LINKS", "ORGANISATIONLINKS", "CAN_EDIT", "CAN_DELETE", "SOCIAL_LINKEDIN", "SOCIAL_FACEBOOK", "SOCIAL_TWITTER"}, {"ORGANISATION_ID", "ORGANISATION_NAME", "BACKGROUND", "IMAGE_URL", "OWNER_USER_ID", "DATE_CREATED_UTC", "DATE_UPDATED_UTC", "VISIBLE_TO", "VISIBLE_TEAM_ID", "VISIBLE_USER_IDS", "CUSTOMFIELDS", "ADDRESSES", "CONTACTINFOS", "DATES", "TAGS", "LINKS", "ORGANISATIONLINKS", "CAN_EDIT", "CAN_DELETE", "SOCIAL_LINKEDIN", "SOCIAL_FACEBOOK", "SOCIAL_TWITTER"})
in
 #"Expanded Column1",

//Table2
 Source = #table({"Start", "Finish"}, {{0, 499},{500, 999},{1000, 1499}}),
//CallFunction
 CallFunction = Table.AddColumn(Source, "CallFunction", each Function(Text.From([Start]),Text.From([Finish]))),
//Function
    #"Expanded CallFunction" = Table.ExpandTableColumn(CallFunction, "CallFunction", {#"Converted to Table", "Column1", {"ORGANISATION_ID", "ORGANISATION_NAME", "BACKGROUND", "IMAGE_URL", "OWNER_USER_ID", "DATE_CREATED_UTC", "DATE_UPDATED_UTC", "VISIBLE_TO", "VISIBLE_TEAM_ID", "VISIBLE_USER_IDS", "CUSTOMFIELDS", "ADDRESSES", "CONTACTINFOS", "DATES", "TAGS", "LINKS", "ORGANISATIONLINKS", "CAN_EDIT", "CAN_DELETE", "SOCIAL_LINKEDIN", "SOCIAL_FACEBOOK", "SOCIAL_TWITTER"}, {"ORGANISATION_ID", "ORGANISATION_NAME", "BACKGROUND", "IMAGE_URL", "OWNER_USER_ID", "DATE_CREATED_UTC", "DATE_UPDATED_UTC", "VISIBLE_TO", "VISIBLE_TEAM_ID", "VISIBLE_USER_IDS", "CUSTOMFIELDS", "ADDRESSES", "CONTACTINFOS", "DATES", "TAGS", "LINKS", "ORGANISATIONLINKS", "CAN_EDIT", "CAN_DELETE", "SOCIAL_LINKEDIN", "SOCIAL_FACEBOOK", "SOCIAL_TWITTER"}}, {#"Converted to Table", "Column1", {"ORGANISATION_ID", "ORGANISATION_NAME", "BACKGROUND", "IMAGE_URL", "OWNER_USER_ID", "DATE_CREATED_UTC", "DATE_UPDATED_UTC", "VISIBLE_TO", "VISIBLE_TEAM_ID", "VISIBLE_USER_IDS", "CUSTOMFIELDS", "ADDRESSES", "CONTACTINFOS", "DATES", "TAGS", "LINKS", "ORGANISATIONLINKS", "CAN_EDIT", "CAN_DELETE", "SOCIAL_LINKEDIN", "SOCIAL_FACEBOOK", "SOCIAL_TWITTER"}, {"ORGANISATION_ID", "ORGANISATION_NAME", "BACKGROUND", "IMAGE_URL", "OWNER_USER_ID", "DATE_CREATED_UTC", "DATE_UPDATED_UTC", "VISIBLE_TO", "VISIBLE_TEAM_ID", "VISIBLE_USER_IDS", "CUSTOMFIELDS", "ADDRESSES", "CONTACTINFOS", "DATES", "TAGS", "LINKS", "ORGANISATIONLINKS", "CAN_EDIT", "CAN_DELETE", "SOCIAL_LINKEDIN", "SOCIAL_FACEBOOK", "SOCIAL_TWITTER"}})
in
    #"Expanded CallFunction"

Which retunrs the error: "Expression.Error: Name "Converted to Table" not found. Make sure it is spelled correctly.

After removing the last step of the query (Expanded CallFunction), I see the table:

What am I doing wrong?

[ImkeF]

The error-message you've posted relates to the last step "Expanded CallFunction" which must fail, also because the column is not expandable yet (no expand arrows in the picture) and the nested curly brackets would also not work to my knowledge. So that step has probably to be rebuilt after the underlying error has been solved:

The error that causes the error-messages in each field of the column itself.

To retrieve it, please don't click on the word "Error" itself, but right next to it in the white space. Then down below the error-message should be shown.

[webportal]

The error seems to be here:

 Source = Json.Document(Web.Contents("https://api.insight.ly/v2.2/Organisations?skip="&Start&"&top="&Finish"& , [Headers=[Authorization="Basic ****", ContentType="application/json"]])),

I want to express this:

Source = Json.Document(Web.Contents("https://api.insight.ly/v2.2/Contacts?skip=0&top=499", [Headers=[Authorization="Basic ***", ContentType="application/json"]])),

No matter how I play with the ", I always get the syntax error:

Comma token expected

[ImkeF]

Ah, syntax-checking in functions really is a pain. But I think I got it:

 Source = Json.Document(Web.Contents("https://api.insight.ly/v2.2/Organisations?skip="&Start&"&top="&Finish&" , [Headers=[Authorization="Basic ****", ContentType="application/json"]])),

Switched &" after "Finish"

[webportal]

I still get the same error in the word Basic

[ImkeF]

Please always post the original error-messages.

But if it "moved" to the Authorization-part already, this has nothing to do with the function-parameters we're passing to it. So please compare carefully with the original code you've posted which returned the correct results.

You can also check/debug the function like this:

//Function = (Start as text, Finish as text) =>

let
Start = "0",
Finish = "499",
 Source = Json.Document(Web.Contents("https://api.insight.ly/v2.2/Organisations?skip="&Start&"&top="&Finish&"&", [Headers=[Authorization="Basic *****", ContentType="application/json"]])),
 data = Source[data],
 #"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
 #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"ORGANISATION_ID", "ORGANISATION_NAME", "BACKGROUND", "IMAGE_URL", "OWNER_USER_ID", "DATE_CREATED_UTC", "DATE_UPDATED_UTC", "VISIBLE_TO", "VISIBLE_TEAM_ID", "VISIBLE_USER_IDS", "CUSTOMFIELDS", "ADDRESSES", "CONTACTINFOS", "DATES", "TAGS", "LINKS", "ORGANISATIONLINKS", "CAN_EDIT", "CAN_DELETE", "SOCIAL_LINKEDIN", "SOCIAL_FACEBOOK", "SOCIAL_TWITTER"}, {"ORGANISATION_ID", "ORGANISATION_NAME", "BACKGROUND", "IMAGE_URL", "OWNER_USER_ID", "DATE_CREATED_UTC", "DATE_UPDATED_UTC", "VISIBLE_TO", "VISIBLE_TEAM_ID", "VISIBLE_USER_IDS", "CUSTOMFIELDS", "ADDRESSES", "CONTACTINFOS", "DATES", "TAGS", "LINKS", "ORGANISATIONLINKS", "CAN_EDIT", "CAN_DELETE", "SOCIAL_LINKEDIN", "SOCIAL_FACEBOOK", "SOCIAL_TWITTER"})
in
 #"Expanded Column1",

This enables you to localize the step in which the error actually occurs.

[webportal]

Hello,

This is the code so far:

let

Function = (Start as text, Finish as text) =>

let
 Source = Json.Document(Web.Contents("https://api.insight.ly/v2.2/Organisations?skip="&Start&"&top="&Finish&" , [Headers=[Authorization="Basic ****", ContentType="application/json"]])),
 data = Source[data],
 #"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
 #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"ORGANISATION_ID", "ORGANISATION_NAME", "BACKGROUND", "IMAGE_URL", "OWNER_USER_ID", "DATE_CREATED_UTC", "DATE_UPDATED_UTC", "VISIBLE_TO", "VISIBLE_TEAM_ID", "VISIBLE_USER_IDS", "CUSTOMFIELDS", "ADDRESSES", "CONTACTINFOS", "DATES", "TAGS", "LINKS", "ORGANISATIONLINKS", "CAN_EDIT", "CAN_DELETE", "SOCIAL_LINKEDIN", "SOCIAL_FACEBOOK", "SOCIAL_TWITTER"}, {"ORGANISATION_ID", "ORGANISATION_NAME", "BACKGROUND", "IMAGE_URL", "OWNER_USER_ID", "DATE_CREATED_UTC", "DATE_UPDATED_UTC", "VISIBLE_TO", "VISIBLE_TEAM_ID", "VISIBLE_USER_IDS", "CUSTOMFIELDS", "ADDRESSES", "CONTACTINFOS", "DATES", "TAGS", "LINKS", "ORGANISATIONLINKS", "CAN_EDIT", "CAN_DELETE", "SOCIAL_LINKEDIN", "SOCIAL_FACEBOOK", "SOCIAL_TWITTER"})
in
 #"Expanded Column1",

//Table2
 Source = #table({"Start", "Finish"}, {{0, 499},{500, 999},{1000, 1499}}),
//CallFunction
 CallFunction = Table.AddColumn(Source, "CallFunction", each Function(Text.From([Start]),Text.From([Finish]))),
//Function
    #"Expanded CallFunction" = Table.ExpandTableColumn(CallFunction, "CallFunction", {#"Converted to Table", "Column1", {"ORGANISATION_ID", "ORGANISATION_NAME", "BACKGROUND", "IMAGE_URL", "OWNER_USER_ID", "DATE_CREATED_UTC", "DATE_UPDATED_UTC", "VISIBLE_TO", "VISIBLE_TEAM_ID", "VISIBLE_USER_IDS", "CUSTOMFIELDS", "ADDRESSES", "CONTACTINFOS", "DATES", "TAGS", "LINKS", "ORGANISATIONLINKS", "CAN_EDIT", "CAN_DELETE", "SOCIAL_LINKEDIN", "SOCIAL_FACEBOOK", "SOCIAL_TWITTER"}, {"ORGANISATION_ID", "ORGANISATION_NAME", "BACKGROUND", "IMAGE_URL", "OWNER_USER_ID", "DATE_CREATED_UTC", "DATE_UPDATED_UTC", "VISIBLE_TO", "VISIBLE_TEAM_ID", "VISIBLE_USER_IDS", "CUSTOMFIELDS", "ADDRESSES", "CONTACTINFOS", "DATES", "TAGS", "LINKS", "ORGANISATIONLINKS", "CAN_EDIT", "CAN_DELETE", "SOCIAL_LINKEDIN", "SOCIAL_FACEBOOK", "SOCIAL_TWITTER"}}, {#"Converted to Table", "Column1", {"ORGANISATION_ID", "ORGANISATION_NAME", "BACKGROUND", "IMAGE_URL", "OWNER_USER_ID", "DATE_CREATED_UTC", "DATE_UPDATED_UTC", "VISIBLE_TO", "VISIBLE_TEAM_ID", "VISIBLE_USER_IDS", "CUSTOMFIELDS", "ADDRESSES", "CONTACTINFOS", "DATES", "TAGS", "LINKS", "ORGANISATIONLINKS", "CAN_EDIT", "CAN_DELETE", "SOCIAL_LINKEDIN", "SOCIAL_FACEBOOK", "SOCIAL_TWITTER"}, {"ORGANISATION_ID", "ORGANISATION_NAME", "BACKGROUND", "IMAGE_URL", "OWNER_USER_ID", "DATE_CREATED_UTC", "DATE_UPDATED_UTC", "VISIBLE_TO", "VISIBLE_TEAM_ID", "VISIBLE_USER_IDS", "CUSTOMFIELDS", "ADDRESSES", "CONTACTINFOS", "DATES", "TAGS", "LINKS", "ORGANISATIONLINKS", "CAN_EDIT", "CAN_DELETE", "SOCIAL_LINKEDIN", "SOCIAL_FACEBOOK", "SOCIAL_TWITTER"}})
in
    #"Expanded CallFunction"

And this is the original error message:

The arrow points to line 6 / 7 with arrows point up to the word Basic.

I think it is because of the quotation marks for the expression that starts wit Basic.

How to express quotation marks inside quotation marks??

For example, how to express this line:

Source = Json.Document(Web.Contents("https://api.insight.ly/v2.2/Contacts?skip=500&top=1000", [Headers=[Authorization="Basic ****", ContentType="application/json"]])),

I'm trying all sorts of comma combinations, etc. but no luck.

Where can I find and fix the error?

Thank you so much for your help!

[ImkeF]

Yes, quotation marks are tricky.

Try duplicating them, if it still doesn't work, take 3 or four until it's working.

[ImkeF]

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.