Importing Data using Excel PowerQuery

Other Halo API Support
1

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

2

I’ve never used powerquery before, how are you sending the authentication header (Ocp-Apim-Subscription-Key)?

3

> 2753093343646559;2:
> 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.

4

Bump

5

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:

> 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.
6

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

7

Not trying to resurrect an old thread, but has anyone figured out the answer to Nj Pr0 and Keggity’s question?

8

I would also like to know