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

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"