close

How To Get Data From Power BI Dataset Via An API

Posted by Kevin Booth on Apr, 23, 2021 03:04

An API or application programming interface is a set of protocols that allows software components to interact over the internet, in a coordinated way. The unit providing a service is called a server and the unit requesting the service is called the client. Let’s scratch the surface of Web API’s and its basic working before we get into how to use it to get data from Power BI in API.

Web API

Most of the web-based services make use of REST API. REST stands for Representational State Transfer, which was introduced by Roy Fielding as a research paper, in the year 2000. The client requests the server by sending a GET request to a URL and the server sends an XML, JSON, or similar file in response. Different languages have different built-in parsers, to extract data from JSON or XML files.

Methods of REST API

  • GET (To read a resource)
  • POST (To create a resource)
  • PUT (To put a resource)
  • DELETE (To delete a resource)

What Are The Principles Of REST API?

Here are the 6 principles of REST API are:

  1. Stateless Protocol
  2. Client-Server architecture
  3. Uniformity
  4. Uses Cache
  5. Multi-Layered System
  6. Code on demand

1) Stateless: Each request an independent entity for the server and it has no memory of the previous request.

2) Client-Server: Logic and GUI is separated to make the architecture more efficient. It can easily be scaled as per requirement.

3) Uniform Interface: REST has a major constraint that ensures uniformity among multiple clients.

  • Identifying the Resource
  • Manipulate resource using representational state
  • Self-explanatory messages

4) Cacheable: To speed up the process and avoid fetching the data from DB frequently, the app is made cacheable and the data can be reused.

5) Layered System: Multi-layered system puts restriction on each layer as to how much it can access. It automatically checks security concerns and leakage of privilege.

REST API is just the URL which is made up of several parts like this:

  • The Endpoint: It is the target URL which is going to give us the result when called for.
  • The Method: Depending on what do you want to do with the API call, this method can be adjusted. All the available methods have already been mentioned above.
  • The Headers: It contains routine info like sender, receiver, encryption used, authentication types and other details which we don’t need to worry about.
  • The Data (Body): This is the actual body part which we needed in the first place. It can be in JSON format, XML format, or another format.

The process to get data in Power BI via an API

Step 1:

Open the Power BI desktop. Click on the “Get Data” button and select the “Web” option as shown in the below screenshot.

Process to get data in Power BI

Step 2:

Click on the “Connect” button. A new window will pop up and you will see two options. (Basic and Advanced)

Get Data From web

Step 3:

You must enter the URL of API which returns the data you want to fetch. Fill in the URL and click on OK. 

Power BI will automatically arrange the results fetched by calling in a key-value pair in the form of rows. You can also use the advance option if you need.

Conclusion

In this tutorial, you saw how we can use API to fetch data from a remote server into the Power BI. You can either set up your own API to test it or use a free API on the internet. POSTMAN is software that can help you test your API service. You can read about more Power BI features that help you with Big Data.

EPC Group Microsoft Gold Partners

You can also reach out to EPCGroup for help with Microsoft Business Intelligence. We have more than 70+ in-house Power BI Developers that can help from Power BI training to full implementation of BI solutions in your organization.

[gravityforms id=41 title=”true” description=”false”]
<div class='gf_browser_unknown gform_wrapper exit_intent_popup_wrapper' id='gform_wrapper_41' ><form method='post' enctype='multipart/form-data' id='gform_41' class='exit_intent_popup' action='/how-to-get-data-from-power-bi-dataset-via-an-api/'> <div class='gform_heading'> <h3 class='gform_title'>Exit Intent</h3> <span class='gform_description'></span> </div> <div class='gform_body'><ul id='gform_fields_41' class='gform_fields top_label form_sublabel_below description_below'><li id='field_41_1' class='gfield gform_hidden field_sublabel_below field_description_below gfield_visibility_visible' ><input name='input_1' id='input_41_1' type='hidden' class='gform_hidden' aria-invalid="false" value='https://www.epcgroup.net/how-to-get-data-from-power-bi-dataset-via-an-api/' /></li><li id='field_41_9' class='gfield gfield_contains_required field_sublabel_below field_description_below gfield_visibility_visible' ><label class='gfield_label' for='input_41_9' >Full Name<span class='gfield_required'>*</span></label><div class='ginput_container ginput_container_text'><input name='input_9' id='input_41_9' type='text' value='' class='medium' placeholder='Full Name' aria-required="true" aria-invalid="false" /></div></li><li id='field_41_6' class='gfield gfield_contains_required field_sublabel_below field_description_below gfield_visibility_visible' ><label class='gfield_label' for='input_41_6' >Email<span class='gfield_required'>*</span></label><div class='ginput_container ginput_container_email'> <input name='input_6' id='input_41_6' type='text' value='' class='medium' placeholder='Email Address' aria-required="true" aria-invalid="false" /> </div></li><li id='field_41_7' class='gfield gfield_contains_required field_sublabel_below field_description_below gfield_visibility_visible' ><label class='gfield_label' for='input_41_7' >Phone<span class='gfield_required'>*</span></label><div class='ginput_container ginput_container_phone'><input name='input_7' id='input_41_7' type='text' value='' class='medium' placeholder='Phone Number' aria-required="true" aria-invalid="false" /></div></li><li id='field_41_10' class='gfield gfield_contains_required field_sublabel_below field_description_below gfield_visibility_visible' ><label class='gfield_label' for='input_41_10' >Company Name<span class='gfield_required'>*</span></label><div class='ginput_container ginput_container_text'><input name='input_10' id='input_41_10' type='text' value='' class='medium' placeholder='Company Name' aria-required="true" aria-invalid="false" /></div></li><li id='field_41_8' class='gfield gfield_contains_required field_sublabel_below field_description_below gfield_visibility_visible' ><label class='gfield_label' for='input_41_8' >Message<span class='gfield_required'>*</span></label><div class='ginput_container ginput_container_textarea'><textarea name='input_8' id='input_41_8' class='textarea medium' placeholder='Type your message here...' aria-required="true" aria-invalid="false" rows='10' cols='50'></textarea></div></li> </ul></div> <div class='gform_footer top_label'> <input type='submit' id='gform_submit_button_41' class='gform_button button' value='Submit' onclick='if(window["gf_submitting_41"]){return false;} window["gf_submitting_41"]=true; ' onkeypress='if( event.keyCode == 13 ){ if(window["gf_submitting_41"]){return false;} window["gf_submitting_41"]=true; jQuery("#gform_41").trigger("submit",[true]); }' /> <input type='hidden' class='gform_hidden' name='is_submit_41' value='1' /> <input type='hidden' class='gform_hidden' name='gform_submit' value='41' /> <input type='hidden' class='gform_hidden' name='gform_unique_id' value='' /> <input type='hidden' class='gform_hidden' name='state_41' value='WyJbXSIsIjEwNTJhNGVmMWMyNzI3YTJmMjdiZTA1NjU4ZDMzYzY3Il0=' /> <input type='hidden' class='gform_hidden' name='gform_target_page_number_41' id='gform_target_page_number_41' value='0' /> <input type='hidden' class='gform_hidden' name='gform_source_page_number_41' id='gform_source_page_number_41' value='1' /> <input type='hidden' name='gform_field_values' value='' /> </div> </form> </div><script type='text/javascript'> jQuery(document).bind('gform_post_render', function(event, formId, currentPage){if(formId == 41) {if(typeof Placeholders != 'undefined'){ Placeholders.enable(); }jQuery('#input_41_7').mask('(999) 999-9999').bind('keypress', function(e){if(e.which == 13){jQuery(this).blur();} } );} } );jQuery(document).bind('gform_post_conditional_logic', function(event, formId, fields, isInit){} );</script><script type='text/javascript'> jQuery(document).ready(function(){jQuery(document).trigger('gform_post_render', [41, 1]) } ); </script>