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) =>
Source = Json.Document(Web.Contents(link&”.json?api_key=”&api_key)),
dataset = Source[dataset],
ExpandAllQuandl = Table.FromRows(dataset[data], dataset[column_names])
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).
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 🙂