Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Anonymous
Not applicable

How to get data from REST API (JSON) in Power BI?

I am familiar with the basics of Power BI, but I don't have knowledge about using REST API's as a data source for Power BI. I know it is possible, but I am hoping that someone is able to elaborate a bit more on this subject so I am able to find the correct methods. 

 

To give you first of all a little bit more background information, I am using a SaaS solution in order to register company contracts. I would like to get these contracts (and related information) in Power BI. The SaaS solution considers the registered contracts as "Assets". The REST API I would like to use to get the contract information responses in JSON and I am able to connect with the REST API by setting up an authorisation code in the admin panel of the SaaS solution. 

 

The REST API uses different requests and within a requests there are different parameters. See an example of a request in the screen capture below:

Capture.PNG

 

How can I retreive the data of all Assets of a certain template and also the specific data of the fields I would like to get in Power BI? I think I am able to get to the data using the url of the request, in this case /assetmgmt/assets/templateID/{templateID}, but how do I include the desired Asset fields?

 

3 REPLIES 3
Nonsensely
Helper I
Helper I

Hello Johajong,

 

These are the steps I use to pull JSON using an API.  I have to do this several times, because our cloud service limits ours APIs to 2000 records.   Once created, I duplicate the query, change the SOURCE text to include an offset in 2000 increments and rename the query - inventory1to2000, inventory2001to4000, etc..  Then I append them into the first query and rename the columns, filter, etc. to the appended query.  

 

Select:   New Source - Blank Query and in the source window enter:

   " = Json.Document(Web.Contents("https://amshowa.leading2lean.com/api/1.0/billofmaterials/?   

           auth=xxxxxxt&site=1&limit=2000&active=True"))"  (your own API)

 

This yields a few fields:

    limit       2000

    data        List

    success TRUE

    offset     0

Click on List and you will get a table with List as column name and numbered rows with "Record" populated

   and an action:  "NAVIGATION: = Source[data]"

On the ribbon, click on Transform then To Table (I let it default the values)

   the action shows: "CONVERTED TO TABLE: = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error)"  This doesn't do much, but adds an opposing arrow icon to the column1 header. 

Click on the opposing arrows in the column header and select columns/fields you want to use.

   the action shows: "CUSTOM1: = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"site", "partnumber", "lastupdated", "description", "shortpartnumber", "obsolete", "lastupdatedby", "active", "created", "lastordered", "cost", "quantity", "id"}, {"site", "partnumber", "lastupdated", "description", "shortpartnumber", "obsolete", "lastupdatedby", "active", "created", "lastordered", "cost", "quantity", "id"})"   If I want to add or delete columns in the table, I do it in here.  

 

You can then filter, sort, merge, etc., just as you would any other table. 

 

Good luck!  

Anonymous
Not applicable

Hi All,

       Can anyone help me how to frame the custom query as i have the api verified in Postman and its returning JSON contents. I have the API key as well but don't know how to build the custom query

hi, i tried for synergi life api to export data into PowerBI. but No luck.. anyhow it works in postman. Please share  your inputs

Peter_Jeyaraj_I_0-1642674457842.png

 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.