![\"Connecting](\"https:\/\/www.sqlshack.com\/wp-content\/uploads\/2020\/08\/connecting-to-a-json-data-source.png\")
<\/a><\/figure>\n\n\n\nLocate the JSON file that contains your dataset and import it into the Power BI desktop. The JSON file that we are going to import contains 100 fictional records of different persons. The first five records in our JSON file look like this:<\/p>\n\n\n\n You can see that each person has four properties, i.e., \u201cPerson_Id\u201d, \u201cPerson_Name\u201d, \u201cPerson_Age\u201d, and \u201cPerson_Gender\u201d. With Power BI, these properties will be converted into columns.<\/p>\n\n\n\n Once the above JSON file is imported, you should see a list with 100 records, as shown in the following screenshot.<\/p>\n\n\n\n If you click on any record, you will see the details of one person, as shown below. You can see the values for the \u201cPerson_Id\u201d, \u201cPerson_Name\u201d, \u201cPerson_Age\u201d, and \u201cPerson_Gender\u201d columns.<\/p>\n\n\n\n To revert to the previous window, click the \u201cX Navigation\u201d button.<\/p>\n\n\n\n To convert the List into the table, click the \u201cTo Table\u201d button from the top menu, as shown in the following screenshot.<\/p>\n\n\n\n Your list will be converted into a table with 1 column. The column contains one record per person, and each record contains 4 values.<\/p>\n\n\n\n To create 4 columns, click on the \u201c\u201d button at the top right corner of the column name as shown in the following figure and click the \u201cOK\u201d button.<\/p>\n\n\n\n You JSON will be converted into a table, as shown below. You can see the four column names that correspond to the properties in JSON files.<\/p>\n\n\n\n To create a visualization in the form of a report, you will need to save this table.<\/p>\n\n\n\n To do so, click the \u201cClose & Apply\u201d button, as shown below. Once you have done that, you will be taken to the Reports View.<\/p>\n\n\n\n From the reports view, select the \u201cClustered column chart\u201d, as shown in the \u201cVisualization\u201d field below. Add the \u201cPerson Gender\u201d column to the Axis and \u201cPerson Age\u201d column to the Value field.<\/p>\n\n\n\n A clustered column chart, such as the one shown below, will be created. The chart shows that the number of persons with gender id 0 is higher than the ones with gender id 1.<\/p>\n\n\n\n With Power BI Rest API calls, you can import data from websites that provide restful APIs for data access. Once the data is imported, you can treat it like any other data file such as JSON or CSV, etc.<\/p>\n\n\n\n In this article, we will be importing information about the 8 different houses from the TV Show Game of Thrones. The data is hosted here, https:\/\/anapioficeandfire.com\/<\/a>. The website provides an API to access books, characters, and houses from the novel series: A Song Of Ice And Fire.<\/p>\n\n\n\n If you click this link, the below will appear in your browser window.<\/p>\n\n\n\n To get data via Power BI Rest API calls, you have to specify \u201cWeb\u201d as the data source in the \u201cGet Data\u201d menu, as shown below. Click the \u201cConnect\u201d button.<\/p>\n\n\n\n In the dialogue box that appears, specify https:\/\/anapioficeandfire.com\/api\/<\/a> in the first part and \u201chouses\/\u201d in the second part. The final URL will be https:\/\/anapioficeandfire.com\/api\/houses\/<\/a>. Click the OK button.<\/p>\n\n\n\n The dataset will be loaded in Power BI Query editor in the form of a JSON file having 10 records.<\/p>\n\n\n\n Each record contains information about one of the houses in the novel series, as shown below:<\/p>\n\n\n\n The rest of the process is similar to what you did in order to expand data imported via JSON files above.<\/p>\n\n\n\n Click on the \u201cTo Table\u201d option from the menu bar. The dataset will be converted to a table.<\/p>\n\n\n\n Again, extend the column by clicking the \u201c<->\u201d symbol at the top of the column name as shown below:<\/p>\n\n\n\n You will see all the column names, as shown below. Click the OK button.<\/p>\n\n\n\n The column names will be extended. If you look at the \u201ctitles\u201d and \u201cseats\u201d columns, you will see that the columns are lists. Expand the columns in the same way that you expanded the original list column by clicking the \u201c<->\u201d button at the top right of the column name as shown below:<\/p>\n\n\n\n Once you expand both the \u201ctitle\u201d and the \u201cseats\u201d column, the dataset should look like this:<\/p>\n\n\n\n You can see that duplicate records have been added when you expand the \u201ctitle\u201d and \u201cseats\u201d column. This is because one house can have multiple titles and seats. Therefore, for each title and seat, a new row is added, resulting in duplicate rows for house names.<\/p>\n\n\n\n Click on the \u201cClose & Apply\u201d button from the top menu to open this dataset in the Query window.<\/p>\n\n\n\n The final step is to create a simple visualization using this data. We will create a simple pie chart that will show the distribution of ancestral weapons by region.<\/p>\n\n\n\n To do this, start by clicking on the \u201cpie\u201d chart option from visualizations, as shown below. In the \u201cLegend\u201d field, add \u201cregion\u201d and in the values, field add \u201cCount of ancestral Weapons\u201d, as shown in the following screenshot.<\/p>\n\n\n\n The following Pie chart will be created as a result. The pie chart shows that almost 50% of the ancestral weapons belong to the region \u201cThe Vale\u201d. Similarly, 11.1% of weapons belong to each of the \u201cThe Reach\u201d and \u201cThe North\u201d region.<\/p>\n\n\n\n In the same way, you can try and add other visualizations to further explore the data.<\/p>\n\n\n\n Power BI can import data from a variety of data sources such as CSV, TSV, JSON files, online flat files, databases, and via power BI REST API calls. In this article, we looked at how to import data via the JSON files and how to use Power BI Rest API calls to import data from websites that provide support for REST APIs. With JSON files, data is imported in the form of records, which you have to first convert to a table. Then you have to expand the table containing JSON records to create one column for each property in a record. With power BI REST APIs, you can import data from a remote location in the form of a JSON file and then explore the data just as you would a local JSON file.<\/p>\n\n\n\n Ref: <\/p>\n","protected":false},"excerpt":{"rendered":" by Ben Richardson In this article, you will see how to import data from JSON files and Power BI Rest API into the Power BI environment. Power BI is a Microsoft application for data analytics and visualization. With Power BI, you can create interactive data visualizations without writing a single line of code. To create a Read More<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[10,621],"tags":[1110,1111],"class_list":["post-4006","post","type-post","status-publish","format-standard","hentry","category-microsoft","category-power-bi","tag-import-data-from-json","tag-import-data-from-rest-api"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/4006","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=4006"}],"version-history":[{"count":2,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/4006\/revisions"}],"predecessor-version":[{"id":4013,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/4006\/revisions\/4013"}],"wp:attachment":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=4006"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=4006"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=4006"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}<\/figure>\n\n\n\n<\/a><\/figure>\n\n\n\n<\/a><\/figure>\n\n\n\n<\/figure>\n\n\n\n<\/a><\/figure>\n\n\n\n<\/a><\/figure>\n\n\n\n<\/a><\/figure>\n\n\n\n<\/a><\/figure>\n\n\n\n<\/a><\/figure>\n\n\n\n<\/a><\/figure>\n\n\n\n<\/figure>\n\n\n\nImporting with Power BI Rest API Calls<\/h2>\n\n\n\n
<\/a><\/figure>\n\n\n\n<\/figure>\n\n\n\n<\/figure>\n\n\n\n<\/a><\/figure>\n\n\n\n<\/a><\/figure>\n\n\n\n<\/figure>\n\n\n\n<\/a><\/figure>\n\n\n\n<\/a><\/figure>\n\n\n\n<\/figure>\n\n\n\n<\/a><\/figure>\n\n\n\nConclusion<\/h2>\n\n\n\n