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.

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