• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Connecting to an OAuth API Like PayPal With Power Query|General Excel Questions & Answers|Excel Forum|My Online Training Hub

You are here: Home / Connecting to an OAuth API Like PayPal With Power Query|General Excel Questions & Answers|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumGeneral Excel Questions & Answe…Connecting to an OAuth API Like Pay…
sp_PrintTopic sp_TopicIcon
Connecting to an OAuth API Like PayPal With Power Query
Avatar
Zeeshan Alam

New Member
Members
Level 0
Forum Posts: 1
Member Since:
September 9, 2020
sp_UserOfflineSmall Offline
1
September 9, 2020 - 5:52 am
sp_Permalink sp_Print

Hi There, 

Can someone please share the PayPal connection query mentioned here https://www.myonlinetraininghu.....ower-query

I'm unable to extract it from the said file.

Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1514
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
2
September 9, 2020 - 8:32 am
sp_Permalink sp_Print sp_EditHistory

Hi Zeeshan,

As I explained in my email, I don't understand how you can't 'extract' the query.  Just open the PQ Editor and it's there.

Below is the query, which you will need to copy/paste into the Query Editor.  Also attached is the original file.

In my email I asked if you had set up your Paypal account and inserted your Client ID and Secret into the query?  It won't work without these 2 bits of information.

Regards

Phil

 

let
// Get the API Token
api_url = "https://api.paypal.com/",
token_path = "v1/oauth2/token",
ClientID = "xxxxxxxx",
Secret = "xxxxxxxx",

EncodedCredentials = "Basic " & Binary.ToText(Text.ToBinary(ClientID & ":" & Secret), BinaryEncoding.Base64),

Token_Response = Json.Document(Web.Contents(api_url,
[
RelativePath = token_path,
Headers = [#"Content-Type"="application/x-www-form-urlencoded",#"Authorization"=EncodedCredentials],
Content=Text.ToBinary("grant_type=client_credentials")
]
)
),

// Get the token from the API response
token = Token_Response[access_token],

// Query the API for Transactions between startDate and endDate and return all fields
path = "v1/reporting/transactions",
endDate = "2020-05-30T00:00:00-0700",
startDate= "2020-05-01T23:59:59-0700",
fields = "all",

data= Json.Document(Web.Contents(api_url,
[
RelativePath = path,
Query = [end_date=endDate, start_date=startDate, fields=fields],
Headers = [#"Authorization"="Bearer "&token,#"Content-Type"="application/json"]
]
)
),

transaction_details = data[transaction_details],

#"Converted to Table" = Table.FromList(transaction_details, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"transaction_info", "payer_info", "shipping_info", "cart_info", "store_info", "auction_info", "incentive_info"}, {"transaction_info", "payer_info", "shipping_info", "cart_info", "store_info", "auction_info", "incentive_info"}),
#"Expanded transaction_info" = Table.ExpandRecordColumn(#"Expanded Column1", "transaction_info", {"paypal_account_id", "transaction_id", "transaction_event_code", "transaction_initiation_date", "transaction_updated_date", "transaction_amount", "fee_amount", "transaction_status", "ending_balance", "available_balance", "invoice_id", "custom_field", "protection_eligibility", "sales_tax_amount"}, {"paypal_account_id", "transaction_id", "transaction_event_code", "transaction_initiation_date", "transaction_updated_date", "transaction_amount", "fee_amount", "transaction_status", "ending_balance", "available_balance", "invoice_id", "custom_field", "protection_eligibility", "sales_tax_amount"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded transaction_info",{"paypal_account_id", "transaction_event_code", "transaction_updated_date"}),
#"Expanded transaction_amount" = Table.ExpandRecordColumn(#"Removed Columns", "transaction_amount", {"currency_code", "value"}, {"currency_code", "value"}),
#"Expanded fee_amount" = Table.ExpandRecordColumn(#"Expanded transaction_amount", "fee_amount", {"value"}, {"value.1"}),
#"Removed Columns1" = Table.RemoveColumns(#"Expanded fee_amount",{"transaction_status", "available_balance"}),
#"Expanded ending_balance" = Table.ExpandRecordColumn(#"Removed Columns1", "ending_balance", {"value"}, {"value.2"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded ending_balance",{{"value", "Amount"}, {"value.1", "Fee"}, {"value.2", "Balance"}}),
#"Removed Columns2" = Table.RemoveColumns(#"Renamed Columns",{"protection_eligibility"}),
#"Expanded sales_tax_amount" = Table.ExpandRecordColumn(#"Removed Columns2", "sales_tax_amount", {"value"}, {"value"}),
#"Renamed Columns1" = Table.RenameColumns(#"Expanded sales_tax_amount",{{"value", "GST"}}),
#"Expanded payer_info" = Table.ExpandRecordColumn(#"Renamed Columns1", "payer_info", {"account_id", "email_address", "payer_name", "country_code"}, {"account_id", "email_address", "payer_name", "country_code"}),
#"Expanded payer_name" = Table.ExpandRecordColumn(#"Expanded payer_info", "payer_name", {"given_name", "surname", "alternate_full_name"}, {"given_name", "surname", "alternate_full_name"}),
#"Removed Columns3" = Table.RemoveColumns(#"Expanded payer_name",{"shipping_info"}),
#"Expanded cart_info" = Table.ExpandRecordColumn(#"Removed Columns3", "cart_info", {"item_details"}, {"item_details"}),
#"Expanded item_details" = Table.ExpandListColumn(#"Expanded cart_info", "item_details"),
#"Expanded item_details1" = Table.ExpandRecordColumn(#"Expanded item_details", "item_details", {"item_code", "item_name", "item_quantity", "item_unit_price", "item_amount", "total_item_amount", "invoice_number"}, {"item_code", "item_name", "item_quantity", "item_unit_price", "item_amount", "total_item_amount", "invoice_number"}),
#"Expanded item_unit_price" = Table.ExpandRecordColumn(#"Expanded item_details1", "item_unit_price", {"value"}, {"value"}),
#"Expanded item_amount" = Table.ExpandRecordColumn(#"Expanded item_unit_price", "item_amount", {"value"}, {"value.1"}),
#"Expanded total_item_amount" = Table.ExpandRecordColumn(#"Expanded item_amount", "total_item_amount", {"value"}, {"value.2"}),
#"Renamed Columns2" = Table.RenameColumns(#"Expanded total_item_amount",{{"value", "Unit Price"}, {"value.2", "Total Amount"}}),
#"Removed Columns4" = Table.RemoveColumns(#"Renamed Columns2",{"store_info", "auction_info", "incentive_info", "invoice_number", "value.1"})
in
#"Removed Columns4"

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Lionel Baijot, Jack Brett, Ben Hughes
Guest(s) 9
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Hayden Hao
Angela chen
Sean Moore
John Chisholm
vexokeb sdfg
John Jack
Malcolm Toy
Ray-Yu Yang
George Shihadeh
Naomi Rumble
Forum Stats:
Groups: 3
Forums: 24
Topics: 6211
Posts: 27238

 

Member Stats:
Guest Posters: 49
Members: 31891
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.