Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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?
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!
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.