Universal JSON-opener for Quandl in PowerBI and Excel/Power Query

Today I discovered JSON (…confess) and Quandl. Mates – this is powerful!

Here comes just a quick function to automatically expand all columns of a time-series at once:

(link as text, api_key as text) =>
let
    Source = Json.Document(Web.Contents(link&”.json?api_key=”&api_key)),
    dataset = Source[dataset],
    ExpandAllQuandl = Table.FromRows(dataset[data], dataset[column_names])
in
    ExpandAllQuandl

dataset[data] is the JSON-element that contains the data as a list of lists and dataset[column_names] is a list of all the column names in the matching order to the data. This is exactly the fitting format for the Table.FromRows-function. So this will create a table with all data in rows and all column names as a header – fully dynamic.

I’ve split up links and api-key so that you can share your workbooks more easily. So you can work with lists of sources and just have to pass your api-key centrally at one place or not at all: Then you will be requested everytime you refresh the data (create another function for that as well so that you only have to pass it in once).

QuandlJason2

Quandl is fantastic: You just register once, get your api-key & it’s free for so many sources!

How I whish I had more time (toolbox looking great in my imagination, seeing incremental loads, custom aggregations, user defined parameters (yet still only in Excel…)… ;.-( ).

If your JSON-record has a different structure, this technique might fit better.

Enjoy & stay queryious 🙂

Comments (3) Write a comment

  1. Hi Imke – I just ran across this, it is very helpful to the “other” side of finance, ie, Wall Street. On issue I am struggling with is the XML Table portion, widely used to extract data quickly.

    Most data is current. For example: https://secure.website.com/api/v1/data.xml will return current financial and market data. Essentially, like you will see with Quandl, etc the user saves the current information and refreshes the next day.

    The set up is to solve a ‘big data’ issue. Push a flat file. However, when historical information is needed (which is quite often) the url is parameteritized:

    https:secure.website.com/api/v1/data.xml?date=20170103

    All of the documentation works well outside and XML Table query. Any ideas?

    Reply

    • Hi Brent,
      sorry, but I don’t understand your question. Is about how to pass the parameter to the query in PowerBI or about the handling of the returned results?
      Pls specify a bit more detailed, thx!

      Reply

  2. Pingback: How to open a complex JSON record in Power BI and Power Query – The BIccountant

Leave a Reply