Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
@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.
@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
Hi @webportal
Have you tried creating a custom function out of your existing query?
Proud to be a Super User!
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/
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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?
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.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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
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"
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
I still get the same error in the word Basic
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.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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!
Yes, quotation marks are tricky.
Try duplicating them, if it still doesn't work, take 3 or four until it's working.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
User | Count |
---|---|
103 | |
88 | |
78 | |
71 | |
69 |
User | Count |
---|---|
113 | |
99 | |
97 | |
72 | |
68 |