• 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
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Getting Started with API’s in Power Query

You are here: Home / Power Query / Getting Started with API’s in Power Query
getting started with api's in power query
July 23, 2020 by Philip Treacy

An Application Programming Interface (API) allows a program to retrieve data from a system.

A 'program' could be something written in a language like Python or PHP, or a program could be your web browser or Excel (using Power Query).

The 'system' is any repository of data, very often web based. So you might be using an API to get data from something like Mailchimp or Paypal.

This article looks at web API's so querying such an API is like loading a web page in your browser.

This post deals with Basic Authentication. I've written a different post describing how to connect to an API using OAuth.

There will be some technical jargon sprinkled through this blog post but the beauty of using Power Query is that you don't need to know what most of it means. Power Query hides the technicalities so you don't need to worry about doing any programming.

Watch the Video

Subscribe YouTube

 

Download the Workbook With Sample Queries

Enter your email address below to download the workbook with the data and code from this post.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook.


How to Make an API Request

If you click this link Queensland Wildlife Data API you've made an API request.

By clicking the link you've asked the website des.qld.gov.au to send you some information and it has done this.

The response sent to your browser is just plain text, but it's in a specific format, JSON. Power Query understands this and can decode it.

Typically an API will return data formatted either as JSON, XML or CSV, but again you don't need to worry about this. Power Query will decode it for you.

To understand how to make an API request the first thing you need to do is look at the documentation for the API.

Let's look at an API provided by the Queensland Government. It provides data about the wildlife found here in sunny Queensland.

Queensland Wildlife API

This API is provided by the Queensland Government and allows public access to information about the wildlife and habitat found in Queensland, Australia.

https://www.data.qld.gov.au/dataset/qld-wildlife-data-api

QLD Wildlife API

It mentions that the data is returned in one of three formats, JSON, XML or CSV, and lower down the page the Data and Resources section provides further information on how to retrieve specific data.

QLD Wildlife API Resources

Let's say we want to get all species in a particular family, I'll click on 'Get species' which takes me to a page with more information on how to query this data.

Information on how to get species from API

I can see that there is a mandatory variable 'family' that the query must supply, and further down the page there are examples of some queries.

So if I want to get the names of all parrots my query will be

https://apps.des.qld.gov.au/species/?op=getspecies&family=psittacidae

If I want the names of another family of animals I need to supply that family name.

I can use the Species Profile Search to see the list of species in the database.

Species profile search

https://apps.des.qld.gov.au/species-search/

If I want to check out the reptiles I can click on Reptilia

reptilia

https://apps.des.qld.gov.au/species-search/?kingdom=animals&class=reptilia

which shows me the family names of the different reptiles.

dragon lizards

Our youngest son has a pet bearded dragon so I'm going to look at the Agamidae family (dragon lizards).

Modifying the example query for parrots, the query I want for dragon lizards will be

https://apps.des.qld.gov.au/species/?op=getspecies&family=agamidae

To get this data into Power Query, click on From Web in the Get & Transform Data section of the Ribbon (I'm using Office 365).

power query get data from web

Enter the query URL and when prompted for authentication, choose Anonymous.

power query query url

power query anonymous authentication

Transform the response and save to table. You can download the query in the example workbook.

NASA API's

NASA provides several API's which are listed here https://api.nasa.gov/

NASA API

To use the NASA API's you are required to generate an API key that is used in the request you make. In this case the API key is just a code that is provided in the query URL to identify you.

But if you are just making a few requests you don't need to generate your own API key, you can use the Demo Key provided by NASA, and that's what I am doing.

I'm going to check out what the weather is like on Mars. Yes there's an API for that.

NASA API

Clicking on Insight: Mars Weather Service API provides more information

Mars Weather API

The InSight Mars Lander sends data to Earth about the Mars climate. At the bottom of the image above you can see the request you need to send to get this data:

https://api.nasa.gov/insight_weather/?api_key=DEMO_KEY&feedtype=json&ver=1.0

I'm only interested in the temperature so I'll discard the other data like wind direction and atmospheric pressure.

In Power Query create a new basic web query, insert the URL, choose anonymous authentication, then transform the data and load to a table.

NASA API Request

NASA API Request Using Anonymous Authentication

Download this query and a reproduction of the NASA Mars Weather page in the example workbook (above).

Stripe API

If you use Stripe to take payments then you can get data about your Stripe account through their API.

Documentation for the Stripe API is here https://stripe.com/docs/api

Stripe require all requests to their API to be authenticated, so you need to get API keys from inside your Stripe account.

Further Reading

Stripe Authentication

Manage Stripe API Keys


Stripe allows you to create test keys so that rather than using your live data, you can use test data while you get comfortable with using the API.

Let's say we want to retrieve a list of all charges (payments into your account), this is documented here: Stripe Charges

Stripe Charges API Request Example

You can see that the API request uses the URL https://api.stripe.com/v1/charges so that's all we need to enter into Power Query

Stripe API Request in Power Query

When prompted, choose Basic authentication, and enter your API key as the Username. No password is required.

Stripe Basic Authentication in Power Query API Request

You'll end up with data like this in Power Query.

Stripe Test API Data

Pagination

API's often restrict the number of records returned. In Stripe's case this limit is set to 10 by default but this can be changed: Stripe API Pagination

By adding a parameter to the URL we can tell Stripe how many records we want returned.

The documentation says that the parameter we need to specify is called limit so all we need to do to return 50 charges is to modify our previous URL to this.

https://api.stripe.com/v1/charges?limit=50

API Authentication

More often than not, API requests will require authentication. Making requests that are authenticated means your requests can be tracked. This is done to prevent abuse (making too many requests), to charge for requests, or just to count/limit requests.

There are different types of authentication.

Anonymous - no username, password or API key is required. The Queensland Wildlife API is like this.

Authentication key in request - An API key is required and is passed as part of the URL. The NASA API uses this approach.

Basic Authentication - a username and/or password is required. Either, or both, can be API keys generated from inside the API system. Stripe uses this method.

OAuth - this is a more complicated authentication process than the others and I will look to cover this in another blog post.

Summary

The hardest part is investigating the API documentation and understanding what data can be retrieved and how to do it.

But once you understand the authentication requirements and the basic structure of the query, modifying queries to retrieve different data is pretty straight forward.

getting started with api's in power query

More Power Query API Posts

Connecting to an OAuth API Like PayPal With Power Query

Connecting to an OAuth API Like PayPal With Power Query

How to connect to API's that use OAuth authentication. As an example I'll be connecting to Paypal to query transaction data. Sample file and query available

More Power Query Posts

Power Query if Statements incl. Nested ifs, if or, if and

How to write Power Query if statements, including nested if, ‘if or’ and ‘if and’, which are easier to write than their Excel counterparts.
power query variables

Power Query Variables 3 Ways

Power Query Variables enable you to create parameters that can be used repeatedly and they’re easily updated as they’re stored in one place.
delete empty rows and columns using power query

Remove Blank Rows and Columns from Tables in Power Query

Delete blank rows and columns from tables using Power Query. Even rows/columns with spaces, empty strings or non-printing whitespace
extracting data from lists and records in power query

Extracting Data from Nested Lists and Records in Power Query

Learn how to extract data from lists and records in Power Query, including examples where these data structures are nested inside each other.
combine files with different column names in power query

Combine Files With Different Column Names in Power Query

Learn how to load data into Power Query when the column names in your data don't match up. Sampe files to download.
power query keyboard shortcuts

Power Query Keyboard Shortcuts to Save Time

Time saving keyboard shortcuts for Power Query that work in both Excel and Power BI. Download the free Shortcuts eBook
remove text between delimiters power query

Remove Text Between Delimiters – Power Query

Remove all occurrences of text between delimiters. There's no in-built Power Query function to do this, but this code does.
power query advanced editor tips

Tips for Using The Power Query Advanced Editor

Tips for using the Power Query Advanced Editor in Excel and Power BI. Watch the video to see these tips in action
pivot unknown variable number of rows to columns

Pivot an Unknown Number of Rows into Columns

How do you pivot rows to columns when you don't know how many rows you're dealing with? It's not as easy as you may think.
try otherwise power query iferror

IFERROR in Power Query Using TRY OTHERWISE

Using TRY..OTHERWISE in Power Query Replicates Excel's IFERROR So You Can Trap and Manage Errors In Your Queries.


Category: Power QueryTag: Power Query API
Previous Post:dynamically list excel sheet namesDynamically List Excel Sheet Names
Next Post:Excel PivotTable Profit and Lossexcel pivottable p&L

Reader Interactions

Comments

  1. Mustapha Lamp

    August 24, 2022 at 2:33 am

    Hi Phil,

    As always great information and explanation. You make it look so easy 🙂

    I have been asked to see if I can pull information using an API but it has quite a complicated authentication process which includes a public key, a secret key, a time-sensitive hash etc..

    I managed to get the connection to work just using the API platform but I am not sure if it is possible to use Power BI/Query to get the data.

    I know you are a busy man but if you have a minute can you let me know if you think it is possible or am I shinning up a greasy pole.

    The details of the API are https://documenter.getpostman.com/view/1762973/UVC6jSsR#intro

    Your opinion would be greatly appreciated. If you say it can be done then I will find a way!!!

    Thanks

    Reply
    • Philip Treacy

      August 26, 2022 at 1:27 pm

      Thanks Mustapha.

      I can’t see that PQ has an SHA encryption function so I’d do this by creating a PHP file as per the example on the API website, then call that from PQ.

      The PHP file can do all the connecting and getting of data from the API, and PQ just calls it to get the result of those calls.

      Regards

      Phil

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

launch excel macros course excel vba course

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

239 Excel Keyboard Shortcuts

Download Free PDF

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

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.