Forum Discussion

nnouchi's avatar
nnouchi
Helper I
7 years ago
Solved

Extracting Data from API and transform into tables

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

 

 

Need Help
Show and Tell
Tutorial Requests
  • ImkeF's avatar
    ImkeF
    7 years ago

    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.

6 Replies

  • ImkeF's avatar
    ImkeF
    Community Champion
    7 years ago

    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.

      • hannahgrobinson's avatar
        hannahgrobinson
        Frequent Visitor
        3 years ago

        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.

  • nnouchi's avatar
    nnouchi
    Helper I
    7 years ago

    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's avatar
      v-jiascu-msft
      Microsoft Employee
      7 years ago

      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.

      Extracting-Data-from-API-and-transform-into-tables

       

       

      Best Regards,
      Dale