# Connecting Analytics APIs to Power BI The FloQast Analytics APIs are a series of endpoints that allow you to pull down a comprehensive view of your Reconciliation and Checklist data. This data includes but is not limited to, sign-offs, activity timestamps, review notes, assigned preparers/reviewers, status, entity, and much more. Some popular use cases for the FloQast Analytics APIs are customizable reporting, data centralization across multiple applications, and operational optimization. In this article, we will look at how to pull our data into Power BI using the FloQast Analytics APIs. Once FloQast Analytics APIs have been enabled for your account, you must create an API key. Information on API key generation can be found [HERE](/quick-start/authentication). Armed with your API key, you can choose between the Basic or Advanced setup. [Basic setup](#basic-setup) is intended for smaller payloads and for ease of use and maintenance. [Advanced setup](#advanced-setup) is intended for any-size payloads and for higher customizability. ## Basic Setup This setup is used for smaller payloads and ease of setup. If you run into any 413 status codes from these setup instructions, you will have to use the [Advanced Setup](#advanced-setup) method. ### Create a Power BI API Connection 1. Within PowerBI, Open PowerQuery by selecting the “Transform Data” option below: br ![Select Transform Data in Power BI](/assets/power-bi-transform.d3a80636fb2597485bb1b01b33f96099f3e4cf930ea592f80ac833a7cbb87575.6903399d.png) br 1. Select the “New Source” drop-down and then the “Web” option: br ![Select New Source, then Web in Power BI](/assets/power-bi-source-web.616d2ec39c8d36caeb95192d3108bdb08c87b2bd9197c8d6ae540940e69dcfce.6903399d.png) br To fill out the details in this modal, we must utilize the documentation provided in our [FQ Developer Portal](/). For this guide, we will utilize the [Analytics Reconciliations](/content/api-reference/openapi/reconciliation-analytics) endpoint, but the same configuration will work for other Analytics endpoints. 1. Select the “Advanced” version of the modal: br ![Select Advanced Version](/assets/power-bi-advanced-version.bec820f96e08be6c2af2eaca9d6953ebd4e513da9f1939cc1944b3966e5b87b3.6903399d.png) br 1. Copy the base API resource on the right-hand panel of our Developer Portal and paste it into the first line of URL parts. *NOTE:* The US, EMEA, and APAC servers have different paths. br ![Copy Base API Resource](/assets/power-bi-base-api-resource.53c7c6e2469b52c2aad21169a5c6a510a376ad2da0cdfd26bdb8c9f92a850ee7.6903399d.png) ![Paste Into URL Parts](/assets/power-bi-url-parts.bdcb3c697c77cc7e687cb0c2b64ce90655dfd8aad26c6a8df2c70c853c70d9d7.6903399d.png) br Next, we will fill in the query parameters we want to use. Query parameters are effectively filters upon our data request. It is likely overkill to pull down lifetime data with each API request. Query parameters allow us to avoid this problem by returning more targeted data. Our query parameters are also available within the [FQ Developer Portal](/content/api-reference/openapi/reconciliation-analytics). When adding query parameters, always start with a “?” and, if you have multiple parameters, concatenate each with ampersands “&”. For this guide, we will use [month] and [year]. Another popular query parameter is [modifiedSince], which only returns records that have changed since a certain date. br 1. Update the month and YEAR values below, then copy and paste the whole string into the second line of URL parts. br ``` ?filter[month]=month&filter[year]=YEAR ``` br ![Update Month and Year](/assets/power-bi-month-year.774aeb99ecef8529d5288ab3e6d3a0448559e09e0217f631e14c163619f1cc78.6903399d.png) br 1. Insert your API key as an HTTP request header parameter named “x-api-key”. - The API key is essentially a long encrypted password that lets FloQast know who is trying to access data, and what data they should be able to access. br 1. Click OK, and voila! br ![Add API Key as Header](/assets/power-bi-api-key.58471c733f902a3101d9fc6b57f94801f018e793d7a6b01227fceedd243a1ba7.6903399d.png) br Power BI will make a request to the FloQast API, and, provided the configuration was done correctly, your analytics reconciliation data will be available in a tabular format within PowerQuery. br ![Power BI Table](/assets/power-bi-table.aaf20824ddc53277233d5a8583bc5312722910d56bace113bbe5eff738448a0b.6903399d.png) br ## Advanced Setup 1. Within PowerBI, Open PowerQuery by selecting the “Transform Data” option below: br ![Select Transform Data in Power BI](/assets/power-bi-transform.d3a80636fb2597485bb1b01b33f96099f3e4cf930ea592f80ac833a7cbb87575.6903399d.png) br 1. Select the "New Source" drop-down and then the "Blank Query" option: br ![Select Blank Query](/assets/power-bi-blank-query.1d57a89d27322b801ce9e99045b5ce98d6841d5a6a99055cd238e9a64ab5a7d7.6903399d.png) br 1. With the new Query1 selected, select the "Advanced Editor" option which will open in a new window. br ![Select the Advanced Editor Option](/assets/power-bi-advanced-editor.f8cab94e3453261daa39acfdb720fe3d684f61ae0f296fb9d8322a7f1ed4f341.6903399d.png) br 1. Delete the existing text and then copy the below code and paste into the box. ```m let BaseUrl = "https://fq-api.floqast.app", // Replace with environment. Currently US but can be EU or AU Endpoint = "/api/v1/analytics/reconciliations", // Replace with desired endpoint QueryParams = [ #"page[size]" = "1000", // Should not be deleted, it can be adjusted, however too large will run into 413 errors, too small will be slower. #"filter[month]" = "january", // Adjust, Add or Remove #"filter[year]" = "2024" // Adjust, Add or Remove ], RequestHeaders = [ #"x-api-key" = "API_KEY" // Replace with your actual x-api-key ], GetPageWithNextLink = (url as text) as record => let Response = try Web.Contents( url, [ Headers = RequestHeaders ] ) otherwise null, ParsedJson = if Response <> null then Json.Document(Response) else null, Data = if ParsedJson <> null and Record.HasFields(ParsedJson, {"data"}) then ParsedJson[data] else {}, NextLink = if ParsedJson <> null and Record.HasFields(ParsedJson, {"links"}) and Record.HasFields(ParsedJson[links], {"next"}) then ParsedJson[links][next] else null in [Data = Data, Next = NextLink], InitialUrl = BaseUrl & Endpoint & "?" & Uri.BuildQueryString(QueryParams), PaginatedData = List.Generate( () => [ CurrentPage = GetPageWithNextLink(InitialUrl), CurrentUrl = InitialUrl ], each (not List.IsEmpty([CurrentPage][Data]) or [CurrentPage][Next] <> null), each if [CurrentPage][Next] <> null then let FullNextUrl = BaseUrl & [CurrentPage][Next] in [ CurrentPage = GetPageWithNextLink(FullNextUrl), CurrentUrl = FullNextUrl ] else [CurrentPage = [Data = {}, Next = null], CurrentUrl = null], each [CurrentPage][Data] ), CombinedDataList = List.Combine(PaginatedData), ConvertedToTable = Table.FromRecords(CombinedDataList) in ConvertedToTable ``` Your query should look like: ![Filled in advanced query editor](/assets/power-bi-advanced-query.bc78fce9d052ad43208117f971bbdff13f85ddd81eb29ac6021b41fb32482f3f.6903399d.png) br 1. Replace any of the values with comments (denoted with "//") to your values. The most important fields to replace would be "API_KEY" and any of the records between the square brackets of QueryParams. The values within the square brackets will be your filters. The Endpoint value is set to reconciliations for this example but can be others as well. 2. Whenever you are done filling those values out, you can click 'Done' in the bottom left to save and close the "Advanced Editor". 3. Once the editor is closed, there will be steps filled out and on the last options there will be an alert to specify how to connect to the API, click "Edit Credentials". - This will only be present if this is your first time setting up PowerBI with our API's ![Alert to edit credentials](/assets/power-bi-edit-credentials.b25eca236d40a8f9d03826a633f84cd2738fa53bb0ffd6d06dbb589446dedea1.6903399d.png) br 1. With the credentials window open, we can maintain the defaults of "Anonymous" and select "Connect". - This tells PowerBI that we already have the credentials to hit FloQast's API which we defined earlier in the headers. ![Selecting Connect Anonymously](/assets/power-bi-specify-credentials.bc5921dd3ea35c2f353957c0f2eb26f9118855e8a77c99436b1b6019b77b672d.6903399d.png) br 1. Voila! Power BI will make multiple requests to our API and display all of your data. - If you run into a 413 status code, go back to the "Advanced Editor" and change the "page[size]" QueryParam to a smaller number. ![Finished advanced query](/assets/power-bi-advanced-table.f1d0ad9caa8e93f43235d268d2d6378a92649c7025a107814b9a391ca0f86a3a.6903399d.png) br ## Last Notes **Congratulations!** You now have full, unfettered access to your Reconciliations (or other business objects) through FQ Analytics APIs. This data gives you, as the user, the power to create any report, dashboard, analysis, workflow, etc. that your business requires. br - You can repeat these steps but insert the Checklist endpoints to pull additional FQ data. - If you have additional questions about functionality or implementation, contact your Accounting Success Manager or support@floqast.com.