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

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.

  • nnouchi's avatar
    nnouchi
    Helper I
    7 years ago

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

     

     

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

      • hannahgrobinson's avatar
        hannahgrobinson
        Frequent Visitor
        3 years ago

        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