{"id":2822,"date":"2018-03-07T03:38:27","date_gmt":"2018-03-07T03:38:27","guid":{"rendered":"https:\/\/zappysys.com\/blog\/?p=2822"},"modified":"2024-03-06T23:53:14","modified_gmt":"2024-03-06T23:53:14","slug":"howto-import-json-rest-api-power-bi","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/howto-import-json-rest-api-power-bi\/","title":{"rendered":"How to import REST API in Power BI (Load JSON \/ SOAP XML)"},"content":{"rendered":"

Introduction<\/h2>\n

\"Introduction<\/a><\/p>\n

In this article, we will learn how to import REST API in Power BI<\/strong>.<\/p>\n

Power BI is a very popular Business Analytic tool used to get business information. It is very popular because it is easy to install, simple to learn and very intuitive. Also, REST API is very popular these days and we wanted to show you a way to integrate them in Power BI with the help of the ZappySys ODBC PowerPack<\/a>. This software includes powerful drivers to query REST API<\/strong> URL, Local XML \/ JSON files<\/strong> and XML SOAP Web Service<\/strong> with simple SQL queries.<\/p>\n

The following video will show how to import REST API in Power BI (JSON or XML Data):
\n[youtube https:\/\/www.youtube.com\/watch?v=-3OPfhMY1aI&w=720&h=405]\n

Requirements<\/h2>\n
    \n
  1. First, you will need to have Power BI Desktop (FREE)<\/a> installed<\/li>\n
  2. In the second place, you will need to have internet access<\/li>\n
  3. On third place, our ZappySys ODBC Power Pack<\/a><\/li>\n<\/ol>\n

    Step-by-Step: Import REST API into Power BI<\/h2>\n

    We’ll walk through the steps to import a REST API into Power BI. The steps outlined below focus on working with JSON APIs, but the same principles can be applied when importing SOAP XML Web Services or local JSON\/XML files.<\/p>\n

    For demonstration purposes, we will utilize a REST API to retrieve data in JSON format. The example URL we’ll use is:<\/p>\n

    https:\/\/services.odata.org\/V3\/Northwind\/Northwind.svc\/Customers?$format=json<\/pre>\n
    This URL leverages OData and presents information in JSON format. Our objective is to use the ZappySys ODBC Power Pack to establish a connection to this URL and retrieve the information seamlessly into Power BI.<\/p>\n
    Create ODBC DSN – JSON Driver<\/h3>\n
    Once ZappySys ODBC PowerPack<\/a> is installed our next step is to Create and configure ODBC DSN. For example purpose, we will use ZappySys JSON Driver but steps are identical for most of ZappySys ODBC Drivers (e.g. XML Driver or CSV Driver)\r\n
      \r\n \t
    1. Search for \"odbc\" in your start menu and click on ODBC (64 bits).\r\n* If you cant find this then you can also go to Start Menu > ZappySys > ODBC PowerPack > Click on ODBC Data Sources (64-Bit)<\/span>. If you don't see ODBC 64 bit then most likely you are running 32-bit OS (So just click first ODBC Data Source)\r\n
      \r\n\r\n\"Open<\/a>\r\n
      Open ODBC Data Source<\/p>\r\n\r\n<\/div><\/li>\r\n \t
    2. Go to User DSN Tab<\/strong> and press Add<\/strong>. If your DSN needs to be accessed by all users or some service account (like SQL Task Scheduler) then click on System Tab<\/a> rather than User Tab.\r\n
      \r\n\"Create\r\n<\/a>\r\n
      Create new ODBC DSN (User DSN Tab)<\/p>\r\n\r\n<\/div><\/li>\r\n \t
    3. Add the ZappySys JSON Driver. It is installed with the ZappySys ODBC PowerPack.\r\n
      \r\n\"Add\r\n<\/a>\r\n
      Add ZappySys JSON \/ REST Driver Connection<\/p>\r\n\r\n<\/div><\/li>\r\n \t
    4. Enter a name for the Data source and configure other necessary properties (e.g. URL \/ Credentials). You can configure properties in two different modes (Simple Mode (Default)<\/strong> or Advanced Mode<\/strong>). For Our JSON Driver Example enter URL and Set Data Format as OData<\/strong> (For Simple Mode Check Input \/ Output Format Tab, For Advanced Mode Check. HTTP Advanced Settings).\u00a0 If your API is not OData compliant or you are not sure then keep it DEFAULT<\/strong>.\r\n
      https:\/\/services.odata.org\/V3\/Northwind\/Northwind.svc\/Customers?$format=json<\/pre>\r\n \r\n
      \r\n\"Configure\r\n<\/a>\r\n
      Configure API Connection - Simple Mode (User Interface Mode)<\/p>\r\n\r\n<\/div>\r\n <\/li>\r\n \t
    5. Here is the Advanced view with all properties in Grid mode.\r\n
      \r\n\"Configure\r\n<\/a>\r\n
      Configure API Connection - Advanced Mode (Property Grid Mode)<\/p>\r\n\r\n<\/div><\/li>\r\n \t
    6. Finally, you can go to preview tab. Click on the select table to generate your default SQL Query for API service and press Preview data<\/strong> to see the magic :).When you click Preview data it parses your SQL Query and sends HTTP Request to fetch Data from JSON service. Once the response is returned it parse nested JSON structure and turns into rows\/columns.\r\n
      \r\n\"Select\r\n<\/a>\r\n
      Select Table for preview<\/p>\r\n\r\n<\/div>\r\n <\/li>\r\n \t
    7. \r\n
      Once you select Table name from drop down, UI generates default query for you with all possible column names for selected table like below.<\/div>\r\n
      Sample Query<\/strong><\/div>\r\n
      select \r\n\t\"CustomerID\",\r\n\t\"CompanyName\",\r\n\t\"ContactName\",\r\n\t\"ContactTitle\",\r\n\t\"Address\",\r\n\t\"City\",\r\n\t\"Region\",\r\n\t\"PostalCode\",\r\n\t\"Country\",\r\n\t\"Phone\",\r\n\t\"Fax\"\r\n from [value]<\/pre>\r\n <\/li>\r\n \t
    8. To review more examples, make sure to click on View Examples<\/strong> button to see many more ways to call API services and extract\/transform data\r\n
      \r\n\"ZappySys\r\n<\/a>\r\n
      ZappySys ODBC Driver Examples - Call JSON \/ REST API<\/p>\r\n\r\n<\/div><\/li>\r\n \t
    9. Now last thing you can try is use Query Builder. Query Builder is an easy way to Build Queries by Overriding certain setting defined on DSN. ZappySys API SQL Query language support WITH Clause which can override settings defined on DSN UI. Usually you can Define Connection related settings on DSN and override Dynamic Settings in your SQL Query so you dont have to create many DSN for each API URL.\r\n
      \r\n\"Using\r\n<\/a>\r\n
      Using API SQL Query Builder<\/p>\r\n\r\n<\/div><\/li>\r\n<\/ol>\r\nTransfer DSN settings<\/strong>\r\n\r\nThere will be a time when you like to create similar ODBC DSN on a totally different machine. If you wish to copy settings of one DSN to different DSN then you can use Load Connection String Feature.\r\n\r\nFirst copy Connection string produced by UI settings on DSN configuration by clicking Copy Connection String Button<\/strong> (Found at the bottom of UI). Then you can go to new DSN and click Load connection string to new DSN by clicking Load Connection String Button<\/strong>.\r\n\r\nUnderstanding ODBC Connection String Format<\/strong>\r\n\r\nZappySys ODBC Drivers can be called in two different ways from your Application (e.g. C#<\/a>, Python<\/a>, JAVA<\/a> )\r\n
        \r\n \t
      1. Using Driver Name<\/strong> in the Connection String (You\u00a0can use Copy Connection String option)<\/li>\r\n \t
      2. Using DSN Name<\/strong> in the Connection String<\/li>\r\n<\/ol>\r\nUsing Driver Name in the Connection String<\/strong>\r\n\r\nSyntax:<\/strong>\r\n
        DRIVER={DRIVER-NAME-GOES-HERE}[;Prop1=xxxxxx][;Prop2=xxxxxx] ....\u00a0[;PropN=xxxxxx]<\/pre>\r\nExamples:<\/strong>\r\n
        DRIVER={ZappySys JSON Driver}\r\nDRIVER={ZappySys JSON Driver};DataPath='c:\\mydata.json'\r\nDRIVER={ZappySys XML Driver};DataPath='http:\/\/myserver\/api\/xml\/getOrders'\r\nDRIVER={ZappySys XML Driver};DataPath='c:\\mydata.xml'<\/pre>\r\nUsing DSN Name in the Connection String<\/strong>\r\n\r\nSyntax:<\/strong>\r\n
        DRIVER={DRIVER-NAME-GOES-HERE}[;Prop1=xxxxxx][;Prop2=xxxxxx] ....\u00a0[;PropN=xxxxxx]<\/pre>\r\nExamples:<\/strong>\r\n
        DSN=MyJsonAPI_DSN\r\nDSN=MyJsonAPI_DSN;DataPath='http:\/\/myserver\/api\/json\/getOrders'<\/pre><\/div>\n
        Connect to REST API data source in Power BI (Connect JSON \/ XML data)<\/h3>\n
        In the previous section, we configured and added the ZappySys drivers in the ODBC Driver Administrator with information to connect to REST API. We queried the REST API data in JSON \/ XML format. Now let's look at how to import REST API data in Power BI using from ODBC connection.\r\n
          \r\n \t
        1. Open Power BI Desktop and select the Get data\u00a0<\/strong>option.\r\n
          \r\n\"Get\r\n<\/a>\r\n
          Get data using Power BI<\/p>\r\n\r\n<\/div><\/li>\r\n<\/ol>\r\n
            \r\n \t
          1. Once Get data is clicked, Go to Other<\/strong> and select ODBC.<\/strong>\r\n
            \r\n\"Import\r\n<\/a>\r\n
            Import ODBC data source in Power BI<\/p>\r\n\r\n<\/div><\/li>\r\n \t
          2. After that select ODBC DSN name from the DSN dropdown<\/li>\r\n \t
          3. Now it's time to import data. Basically, there are two modes to import data. Table Mode<\/strong> and Query Mode<\/strong>. Query mode is the most common but we will show you both ways.<\/li>\r\n \t
          4. Import using Power BI Query Mode:\u00a0\u00a0<\/strong>Select your DSN and click Advanced Option to enter custom SQL Query to Import your REST API data. You can use ODBC DSN Data sources Preview tool to generate SQL Query. For example you can enter query like below. If you are not sure use Query builder (Found on Driver Preview Window)\r\n
            SELECT * FROM $\r\nWITH(SRC='https:\/\/my-api-url')<\/pre>\r\n
            \r\n\"Import\r\n<\/a>\r\n
            Import JSON \/ REST API data in Power BI using SQL Query Mode<\/p>\r\n\r\n<\/div>\r\n <\/li>\r\n \t
          5. Import using Power BI Table Mode:\u00a0 <\/strong>If you don't specify SQL query under advanced options then you may get a chance to select Table name to import and Credentials. If Power BI asks for credentials then select Windows<\/strong> and connect.\r\n
            \r\n\"Select\r\n<\/a>\r\n
            Select credentials for ODBC Source<\/p>\r\n\r\n<\/div><\/li>\r\n \t
          6. Once you enter select table, you can choose Select data to import and click OK (Below screeenshot is for the Table mode import when no SQL specified. You can pick desired table to import)\r\n
            \r\n\"Select\r\n<\/a>\r\n
            Select Table to import in Power BI<\/p>\r\n\r\n<\/div><\/li>\r\n \t
          7. You can also display data in map using Map visualization like below. Press the map and check Address this option will display the addresses in a map.\r\n
            \r\n\"see\r\n<\/a>\r\n
            See Address data in power bi map<\/p>\r\n\r\n<\/div>\r\n
            \r\n\"Map\r\n<\/a>\r\n
            Map visualization in Power BI<\/p>\r\n\r\n<\/div><\/li>\r\n \t
          8. Now, let's display data in Table Format. Select in values more columns and select the data grid to visualize the data.\r\n
            \r\n\"Select\r\n<\/a>\r\n
            Select columns in Power BI<\/p>\r\n\r\n<\/div><\/li>\r\n \t
          9. Once data is displayed, Right click on the data and select Show Data<\/strong>.\r\n
            \r\n\"show\r\n<\/a>\r\n
            Show data in Power BI<\/p>\r\n\r\n<\/div><\/li>\r\n \t
          10. The data will be displayed\r\n
            \r\n\"View\r\n<\/a>\r\n
            read json information data<\/p>\r\n\r\n<\/div><\/li>\r\n<\/ol><\/div>\n
            Publish Power BI dashboard<\/h3>\n
              \n
            1. In order to publish the report, press Publish<\/strong><\/li>\n
            2. Press Save<\/strong>\n
            3. Select a workspace for the report<\/span>\n
            4. And that is all. You have now your report ready.<\/li>\n<\/ol>\n
              <\/h2>\n
              Import data using ODBC ConnectionString (DSN-less)<\/h2>\n
              Let’s explore the process of importing data using the ODBC Driver without the need for configuring an ODBC DSN.<\/p>\n
              When importing data, you have the option to either use an ODBC DSN or a Direct ConnectionString. This section will guide you through the steps of utilizing the ODBC ConnectionString.<\/p>\n
              After opting for the ODBC option to retrieve data, you will encounter the “Select ODBC Data Source” screen. To proceed without a DSN, follow these steps:<\/p>\n
                \n
              1. Change the DSN dropdown to “(none).”<\/li>\n
              2. Enter the full ODBC ConnectionString, adhering to the syntax below. You can construct your own connection string using the ODBC Driver UI and utilize the “Copy ConnectionString” option.
                \nSyntax:<\/strong>\u00a0 Driver={Your Driver Name}[;Property1=value][;Property2=value]……<\/span><\/li>\n
              3. Here is a screenshot with ConnectionString Setting\n
              4. Click “Next” to select the authentication method. Choose “Windows” and input the password value if necessary. If your connection string already includes the Password attribute, there’s no need to worry.\n
                \"Import<\/a>
                Import Data from ODBC Driver – using Connection String – With Password Attribute<\/p><\/div><\/li>\n<\/ol>\n
                POST data to REST API URL<\/h2>\n
                Up until now, we’ve explored the process of fetching data from URLs and importing it into Power BI. However, in certain scenarios, APIs may necessitate the use of the PUT or POST method. These methods involve submitting parameters in the body and receiving a response.<\/p>\n
                For a more in-depth understanding of how to seamlessly POST data to a URL in Power BI, watch the informative video below.<\/p>\n
                <\/div><\/div>
                <\/div>
                <\/div>
                <\/div><\/div><\/div>