Using CAPI with Excel

Microsoft Excel can interact with CAPI endpoints that use HTTP GET and provides the ability to deserialize and transform the resulting JSON payload.

Connecting to CAPI

Accessing data from Excel can be done by using Excel’s Data > From Web feature and specifying a CAPI endpoint.

Authentication

If the Access Web Content dialog appears, set the authentication type to Basic and enter credentials that are authorized to execute the API endpoint. CAPI endpoints always use SSL so credentials are encrypted during transmission.

Transforming Data

The HTTP response provided by the CAPI endpoint will be deserialized and displayed in Power Query Editor. Most data is presented as either a singular root object that contains detail records or as a collection of object records. You will need to use Power Query’s Applied Steps to transform this data into a format suitable for your application. This will often involve expanding sub-objects to reach data of interest.

Using Parameters

It is possible to dynamically supply parameter values from Excel to CAPI endpoints that require them. One way is to add an Excel table into Power Query so that its values can be referenced from other queries. In the following animation, we modify the M code of a CAPI query to references table values so that the URL’s query string is updated to match. Specifically, the source is changed as indicated below.

Related

Please refer to Power Query Help for additional information.