Earlier versions of Excel with Microsoft Power Query installed, or later versions of Excel with Get & Transform installed by default are able to collect data through Envizi's web service API.

The steps and screenshots below are based on Microsoft Excel version 2013 with Power Query installed. The flow of steps should be similar to other versions of Excel.

  1. Open Excel and go to Power Query tab → From Web and enter the API URL in the pop up form. Click Ok (for details on how to construct the API URL, check out the API Technical Documentation).



  2. If it is the first time you are setting this up, Excel would prompt to ask you to input your connection credentials. Click Basic from the left hand side panel, and then input your Envizi user name and password. Leave the last field default and click Connect.

  3. The Query Editor should pop up. Once everything is loaded, click To Table under the Transform tab. Click Ok in the pop up form.



  4. All of your data should be organized into a single column. Click the two-way expansion arrow beside the column header, in the pop up form un-select the last option 'Use original column name as prefix', and then click Ok.



  5. The data will now be presented as a table format in Query Editor. You may now continue with normal Power Query transform operations such as rename columns, re-arrange column orders, changing column data types etc. Since all data returned from API is in string format by default, so for a minimum it is recommended to:

    1. Change the data type of all date related columns to be Date, and

    2. Change the data type of all numeric columns to be numeric data type (Decimal Number, Whole Number etc.)

      This will facilitate the analysis and reporting in the data model.





  6. Click Close & Load to save the query and populate all data in a spreadsheet table. Depending on the amount of data to be retrieved, this may take some time as Excel needs to download all requested data from Envizi.



  7. You can also choose to load the data into a Power Pivot model, and use multiple API queries to build your own Power Pivot model, just as you would do for any Power BI project.


API Crendentials

The User Name and Password you have supplied is stored in Excel and it will be re-used for every subsequent time when you access the API. If you would like to change the credential, it can be found under Power Query → Data Source Settings.

The credential is not transferable with other users, which means if the file is opened by users other than you, they will be prompted to enter their own Envizi user name and password in order to gain data and access to the report content.


Further Readings

Power Query (Get & Transform in newer versions) together with Power Pivot are powerful add-on in Excel which allow you perform advanced data analysis and create sophisticated data models, in addition to the incumbent Excel pivot tables. With Power Pivot, you can mash up large volumes of data from various Envizi API reports, perform information analysis rapidly, and share insights easily.

Read the official Microsoft documentation on Power Pivot for more advanced reporting options using Excel and the Envizi API.