Power Query Overview

Vitruvi uses a RESTful API to interact with its services. It uses standard HTTP requests and responses with JSON objects. Familiarity with API requests is recommended when trying to query Vitruvi from Power Query's M language.

This documentation will provide example files using Power BI, however the same methods will also apply to using Power Query in Excel.

Specifically, the M language method for querying from web APIs is the Web.Contents() method.

👍

Sample Power BI File

A sample PowerBI file can be downloaded from here and contains most of the examples outlined in this documentation.

Vitruvi's primary APIs used for Power Query, or any other 3rd party reporting product, are the Raw and _Query APIs. See Available APIs. It is likely that most users will find they will need to call on both types of APIs for a complete reporting solution, although the _Query APIs are more likely to be the most useful when first starting out.

When querying from Vitruvi, and to avoid web timeout issues, the use of paging is required. A function and examples on how to use it are provided here.

It is critical that data models account for performance when querying data from Vitruvi. Open, unfiltered queries will result in long query times, degredated Vitruvi performance and slow data models in Power BI or Excel.

The provided function to query Vitruvi's APIs has built in functionality to limt the fields that are selected as well as to filter the results.

🚧

Filtering

It is highly recommended that the filtering and selection criteria are used, and that the user does NOT attempt to retrieve entire data sets.