This article explains how to add Popdock as a data source in Excel so you can work with live data from your connected environments. You’ll learn how to connect Excel to Popdock endpoints and import data using multiple formats, including JSON, XML, and OData.
How to Add an API Endpoint
1. From the main menu, select API.
2. Select Add endpoint.
3. Complete the Add endpoint form with the name for the endpoint, the connector you want to use, the company if needed, and the list you want to use. In this example, the Business Central connector is being used, along with the Cronus company, and the Sales transaction list. If you have a favorite saved, you can also select your favorite. Once you have it completed, select Add.
4. Your API endpoint is now created. Select the Edit icon and then select API endpoint. You can then select Copy to copy the URL. Now you can go to Excel to configure the data connection to the endpoint. When using the Data endpoints URL for Get data, you can modify the format to be JSON, XML, or OData. The next sections will show you how to add each format type.
Adding a JSON Data Source
1. Open a new blank worksheet in Excel.
2. Click the Data menu at the top.
3. Click Get Data, click From Other Sources, and then select From Web. If you have the From Web option already at the top, click From Web.
4. The From Web window will open. Click the radio button to choose Advanced.
5. Here you want to add your API Endpoint URL in the URL parts. Then click the dropdown for HTTP request header parameters. Even though it appears as a dropdown, you have to type in the word Authorization. Then for the box next to Authorization, type the word Bearer, add a space, and then paste your token from Popdock.
6. Once this is completed, click OK.
7. A new window will appear to configure the Data Source. Make sure you click List at the top of the column and then click the To Table button.
8. When the To Table window appears, keep the defaults, and click OK.
9. Now you have a table with Column1 at the top and each row shows Record.
10. Click the icon to the right of Column1 and you will see all the columns from your Popdock list. Here you want to uncheck any columns you don’t need, then at the bottom uncheck the box that says “Use original column name as prefix”. This is so Column1 doesn’t get added in front of every column name that is about to be added to the sheet. Once you are ready, click OK.
11. Excel will then display a preview of what the table will look like using the JSON from the API Endpoint. If everything looks good here, you can click the Close & Load button at the top left and your new worksheet that pulls data from your JSON API Endpoint is ready to use.
Adding an XML Data Source
1. Open a new blank worksheet in Excel.
2. Click the Data menu at the top.
3. Click Get Data, click From Other Sources, and then select From Web. If you have the From Web option already at the top, click From Web.
4. The From Web window will open. Click the radio button to choose Advanced.
5. Here you want to add your API Endpoint URL in the URL parts. Then click the dropdown for HTTP request header parameters. Even though it appears as a dropdown, you have to type in the word Authorization. Then for the box next to Authorization, type the word Bearer, add a space, and then paste your token from Popdock.
6. Once this is completed, click OK.
7. A new window called Navigator will appear. Click your table shown under the API Endpoint.
8. This shows a preview of what the data will look like. You can modify the columns by clicking Transform Data.
9. In the Power Query Editor window, on the right, click the X to remove Changed Type and then click the X to remove Navigation. Now you have the raw Source.
10. Since you only want the table, right-click the Name column and choose Remove.
11. Now you can click the icon to the right of Table and uncheck any columns here that you want removed from your Excel list. Then uncheck the bottom checkbox that says “Use original column name as prefix”. This is so it doesn’t add the word Table to each column name. Click OK once you are done.
12. View the preview of the table and if everything looks good to you, you can click Close & Load to view the data in a new sheet.
Adding an OData Data Source
1. Open a new blank worksheet in Excel.
2. Click the Data menu at the top.
3. Click Get Data, click From Other Sources, and then select From Web. If you have the From Web option already at the top, click From Web.
4. The From Web window will open. Click the radio button to choose Advanced.
5. Here you want to add your API Endpoint URL in the URL parts. Then click the dropdown for HTTP request header parameters. Even though it appears as a dropdown, you have to type in the word Authorization. Then for the box next to Authorization, type the word Bearer, add a space, and then paste your token from Popdock.
6. Once this is completed, click OK.
7. A new window will appear for Power Query Editor showing your OData data source. Click List shown next to value.
8. This then shows you a List column with records below in rows. Make sure you click the List at the top of the column, then click the To Table button.
9. When the To Table window appears, keep the defaults, and click OK.
10. Now you have a table with Column1 at the top and each row shows Record.
11. Click the icon to the right of Column1 and you will see all the columns from your Popdock list. Here you want to uncheck any columns you don’t need, then at the bottom uncheck the box that says “Use original column name as prefix”. This is so Column1 doesn’t get added in front of every column name that is about to be added to the sheet. Once you are ready, click OK.
12. Excel will then display a preview of what the table will look like using the OData from the API Endpoint. If everything looks good here, you can click the Close & Load button at the top left and your new worksheet that pulls data from your OData API Endpoint is ready to use.
Questions on creating API Endpoints for Excel? Contact support@eonesolutions.com.
