Extracting Data from API and transform into tables

[nnouchi]

Greetings Power BI community,

I am having trouble understanding the steps necessary to extract and format data from an API source to be used practically in Power BI. 

This is the outcome of my dataset when I load the api through the Power Query editor. The first thing I'd like to know is how to extract the list of columns and match those columns to the data source. When I expand on the list for "data", I get directed to another table that is comprised of all of the data values in a multi-row format:

Going one more level deeper, I have an outcome of of one column with a bunch of row values that should be tied as one row record instead of the 13 rows I have in this upcoming image:

Now is it possible to transform the data set to include all column headers with each specific data point?

I have been able to create reports using APIs through Python but I want the ability to refresh this data source on a weekly basis and have reactive visualizations.

I apologize for the length of this question and my knowledge of M is very limited therefore any help would be greatly appreciated.

This is the source I'm attempting to query:

https://www.quandl.com/api/v3/datasets/LME/PR_NI.json?api_key=**************

Thanks,

Nicolas Nouchi

[ImkeF · Accepted solution]

Hi @nnouchi,

there is a very easy way to retrieve the TS-data from Quandl:

let
    Source = Json.Document(Web.Contents("https://www.quandl.com/api/v3/datasets/LME/PR_NI.json?api_key=xxxxxxxx")),
    dataset = Source[dataset],
    ExpandAllQuandl = Table.FromRows(dataset[data], dataset[column_names])
in
    ExpandAllQuandl

Works with all sources and will definitely refresh in the service as well.

[nnouchi]

@ImkeF Thank you very much, that was easier than I expected.

[hannahgrobinson]

Hi @nnouchi @ImkeF could you please help me understand where the solution script should be written? I am trying to achieve a similar result with an API data source. Thank you so much.

[hannahgrobinson]

Hi again 🙂 I have adjusted the code you shared to include the API URL I am working with within the query editor. My apologies for the very novice questions, but I am not sure how to troubleshoot the error I'm recieving "The field 'dataset' of the record wasn't found." Thank you so much for any guidance! 

let
    Source = Json.Document(Web.Contents("https://clinicaltrials.gov/api/query/study_fields?&max_rnk=1000&fmt=json&fields=BriefTitle,InterventionName,InterventionType,LastKnownStatus,LeadSponsorName")),
    dataset = Source[dataset],
    ExpandAll = Table.FromRows(dataset[data], dataset[column_names])
in
    ExpandAll

[nnouchi]

Okay, I have figured out how to extract the dataset of values and just renamed the columns as they would be in the column headers, however, I don't think this would work with any updated refresh since the data had to be manipulated and transformed.

[v-jiascu-msft]

Hi @nnouchi,

I'm glad you made it. You can refresh it if you did all the manipulations and transformations in the Query Editor. Please refer to the snapshot below. The steps record all your operations. They will be applied to the new data. Please give it a try.

Best Regards,
Dale