Forums / Support / Halo API Support

Importing Data using Excel PowerQuery

OP Nj Pr0

Hi Guys,

Im trying to import my matches into Excel using powerquery and i am having an error. I send the request url and than Powerquery asks me for a subsription key and i use the one provided and it does not work. It says invalid credentials...

Any help would be appreciated.

Thanks
I've never used powerquery before, how are you sending the authentication header (Ocp-Apim-Subscription-Key)?
I've never used powerquery before, how are you sending the authentication header (Ocp-Apim-Subscription-Key)?
Yes and ive tried both keys, generating new keys, and it still says i dont have the credentials.
Bump
You'll need to write a query from scratch (you can't use the wizard as far as I can tell) because you can't sent the authorisation header.

An example to load the Medals Metadata using Excel 2013 with PowerQuery installed.
  • Click the Power Query menu item.
  • Click the From Other Sources drop-down.
  • Click Blank Query.
  • Query Editor: Click the Home menu item.
  • Query Editor: Click Advanced Editor.
  • Advanced Editor: Review and copy the following:
Quote:
let
Source = Web.Contents("https://www.haloapi.com/metadata/h5/metadata/medals", [Headers=[#"Ocp-Apim-Subscription-Key"="YOUR_API_KEY_HERE"]])
in
Source
  • Advanced Editor: Click Done.
  • Query Editor: Double click the new Web File.
  • Query Editor: Click the (List Tools) Transform menu item.
  • Query Editor: Click To Table and click OK.
  • Query Editor: Click the Expand icon (next to Column1). Leave all the columns checked and uncheck Use original column as prefix.
  • Query Editor: Click the Expand icon (next to SpriteLocation). Leave all the columns checked and uncheck Use original column as prefix.
  • Query Editor: Click the Home menu item.
  • Query Editor: Click Close and Load.
  • Review data.
Running into the same problem as Nj Pr0

When I attempt to use my API token to query via Excel 2016 I consistently get a generic "Unauthorized" error. I can't get past the "Advanced Editor: Click Done" step above. However, when I execute a query using the "Try It" button in the Halo API Documentation, the query executes with my same API token, no problem. Any tips on what I may be doing wrong?

Here's my contents of the new blank I'm writing in Excel:
let
Source = Web.Contents("https://www.haloapi.com/stats/h5/servicerecords/arena?players=keggity", [Headers=[#"Ocp-Apim-Subscription-Key"="MY_API_KEY"]])
in
Source
Not trying to resurrect an old thread, but has anyone figured out the answer to Nj Pr0 and Keggity's question?
I would also like to know