Log In
Register
Home
Active Member
July 16, 2019
Offline
Hello, I'm able to connect to PayPal as described in this article Connecting to an OAuth API Like PayPal With Power Query by Philip Treacy. However, I'm limited to loading one page at a time, for a total of 500 rows. How can this query be modified to load into Excel all the pages returned by the API call?
I know it's possible for the query to iterate through the pages as I've done it myself in other use cases but I can't for the life of me figure out how to make it work using the PayPal connection query.
Any help with loading the entire list of records within the specified date range will be greatly appreciated!
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Hi Toynk,
You have to use API query parameters: https://developer.paypal.com/d.....parameters
First, query the API to extract the total count of items with the count parameter.
TotalCount= query: api-m.sandbox.paypal.com/v1/invoicing/invoices?count
Knowing the total count, choose your page size , calculate the number of pages:
Pages=Number.Roundup(TotalCount / 50)
and start a loop or a recursive query, increasing the page number, until you reach the last page index:
api-m.sandbox.paypal.com/v1/invoicing/invoices?page=1&page_size=50
api-m.sandbox.paypal.com/v1/invoicing/invoices?page=2&page_size=50
Active Member
July 16, 2019
Offline
Thanks for the reply. Does not answer my question however. I need to know how to modify the M code in the example. I can't figure it out.
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Paste the query into a blank new query:
let
JsonData = Json.Document(Web.Contents(api_url,
[RelativePath = path,
Query = [page=PageNumber, page_size=50],
Headers=[#"Accept" = "application/json", #"Authorization" = "bearer " & token]]),1252),
ResponseData=JsonData[transaction_details],
AllData= if PageNumber+1<=TotalPages then
try ResponseData & GetNextPageData(api_url,path,PageNumber+1,TotalPages, token) otherwise ResponseData
else
ResponseData
in
AllData
The name of this query must be: GetNextPageData
The following section from the example query:
[
RelativePath = path,
Query = [end_date=endDate, start_date=startDate, fields=fields],
Headers = [#"Authorization"="Bearer "& token,#"Content-Type"="application/json"]
]
)
),
transaction_details = data[transaction_details],
Must be replaced with:
[
RelativePath = path,
Query = [count],
Headers = [#"Authorization"="Bearer "& token,#"Content-Type"="application/json"]
]
)
) ,
TotalPages=Number.RoundUp(TotalCount/50),
transaction_details=GetNextPageData(api_url, path, 1, TotalPages, token),
Most likely, will not work the same for all API providers, each API is unique: some providers will have the next page link in the current response, others will provide pagination meta data in response, like: response[meta][pagination], some API's expect you do to a blind loop until you get an error (they don't provide a count or pagination, so you don't know where you should stop).
If you think that you'll be able to use the code for any API like Paypal as your topic says, my guess is that it will not work. You have to study each API separately, see what elements they provide in the response and adjust the actions according to their documentation.
Active Member
July 16, 2019
Offline
Thanks again for your assistance!
I'm getting two errors. First, in the GetNextPageData query I'm getting the following:
An error occurred in the ‘’ query. Expression.Error: The name 'RelativePath' wasn't recognized. Make sure it's spelled correctly.
In the original PayPal query after your modification, I'm getting the following error:
Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?
Did I do something wrong that is causing these?
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Copy again the GetNextPageData function from above, was missing an opening [ just before RelativePath.
Copy again the changes indicated, each line should end with a comma.
Active Member
July 16, 2019
Offline
I did as you describe and I'm not getting this error:
Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?
This error is related to:
TotalCount= Json.Document(Web.Contents(api_url,
[
RelativePath = path,
Query = [count],
Headers = [#"Authorization"="Bearer "& token,#"Content-Type"="application/json"]
]
)
) ,
TotalPages=Number.RoundUp(TotalCount/50),
transaction_details=GetNextPageData(api_url, path, 1, TotalPages, token),
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
I don't see errors in the code.
With Advanced Editor open, if you press Show Error, what section of code gets highlighted?
Active Member
July 16, 2019
Offline
I was able to finally get this to work. First of all, I owe you many thanks for all your help. Would not have been able to do this from scratch on my own. Your help is very much appreciated. Below are the errors I needed to resolve to get it to work.
As I mentioned in an earlier post, I was getting the following error:
Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?
This error was caused by the step below and specifically, Query = [count] was the culprit.
TotalCount= Json.Document(Web.Contents(api_url,
[
RelativePath = path,
Query = [count],
Headers = [#"Authorization"="Bearer "& token,#"Content-Type"="application/json"]
]
)
) ,
TotalPages=Number.RoundUp(TotalCount/50),
transaction_details=GetNextPageData(api_url, path, 1, TotalPages, token),
There were also datatype conversion issues with the TotalPages variable and the pagination math in the function. I reviewed the PayPal API docs and found that the API response includes total_pages so I used it instead of calculating a page total. So my query now looks like this:
TotalCount= Json.Document(Web.Contents(api_url,
[
RelativePath = path,
Query = [end_date=endDate, start_date=startDate, fields="all", page_size="500"],
Headers = [#"Authorization"="Bearer "& token,#"Content-Type"="application/json"]
]
)
) ,
TotalPages=TotalCount[total_pages],transaction_details=GetNextPageData(api_url, path, 1, TotalPages, token),
There was also a datatype issue in the function related to the page number so I made the change in bold below. Those were my only two issues and now it works perfectly. Thanks again!!
(api_url, path, PageNumber, TotalPages, token)=>
let
JsonData = Json.Document(Web.Contents(api_url,
[RelativePath = path,
Query = [end_date=end_date, start_date=start_date, page=Number.ToText(PageNumber), page_size="500", fields="all"],
Headers=[#"Accept" = "application/json", #"Authorization" = "bearer " & token]]),1252),
ResponseData=JsonData[transaction_details],
AllData= if PageNumber+1<=TotalPages then
try ResponseData & GetNextPageData(api_url,path,PageNumber+1,TotalPages, token) otherwise ResponseData
else
ResponseData
in
AllData
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
great, thanks for feedback! 
1 Guest(s)
