--- title: "Excel and Power BI" slug: "excel-and-power-bi" updated: 2025-01-08T10:45:28Z published: 2025-01-08T10:45:28Z canonical: "docs.mapp.com/excel-and-power-bi" --- > ## Documentation Index > Fetch the complete documentation index at: https://docs.mapp.com/llms.txt > Use this file to discover all available pages before exploring further. # Excel and Power BI This video demonstrates how to update your Mapp Intelligence Analysis directly within Excel using the Intelligence API. The exact paths and designations may differ depending on your Excel version. [Embedded content](https://player.vimeo.com/video/919524506?badge=0&autopause=0&player_id=0&app_id=58479) All the steps we mention in the video are listed below. | Before anything, you must have API activated for your Mapp Intelligence Account. If not already active, please reach out to your CSM. | | --- | | Step | Navigation path | Description | | --- | --- | --- | | 1 | *User Management > Roles > Account Configuration* | Make sure the user has permission to access the API. 1. Go to *User Management > Roles*, and select the role that should have API access. 2. Enable **Intelligence API**. | | 2 | *Settings > API Client IDs* | 1. Click the configuration symbol, then click **API Client IDs**. 2. Select **Create New Client ID** 3. Add a description, such as the user's name. 4. Copy the Client ID and Secret to your clipboard and keep them somewhere safe! You will need them any time you use the API. Find more information [here](/apidocs/how-to-grant-access-to-the-intelligence-analytics-api). | | 3 | *Visitors > Time > Weeks* | 1. Go to *Visitors > Time > Weeks* and add **Visits**, **Qty Orders**, **Conversion Rate %**. 2. Set in the calendar dynamic time frame **Current Year until Yesterday**. 3. In the upper right-hand corner, select the menu and **Copy JSON Configuration**. 4. Paste the JSON into an editor. 5. Replace all the single quotation marks (") with double quotation marks (""). | | 4 | Excel file configuration | 1. [](https://cdn.document360.io/554b9b98-6720-4d8b-9919-c7b203d72648/Images/Documentation/JSON_API_powerBI_demo_version1_5(1).xlsx)JSON_API_powerBI_demo_version1_528.66 KB[](https://cdn.document360.io/554b9b98-6720-4d8b-9919-c7b203d72648/Images/Documentation/JSON_API_powerBI_demo_version1_5(1).xlsx) and open the example sheet. 2. Enable external data connections ![](https://documentation.mapp.com/download/attachments/62750807/image2024-4-12_12-2-0.png?version=1&modificationDate=1712916121000&api=v2) 3. Under the **Data** tab, select *Get Data > Launch Power Query Editor*. 4. Change **Privacy Settings** - **Window OS**: 1. Go to *File > Options > Settings > Query Options > Current WorkBook > Privacy* 2. Under **Privacy Levels**, Select **Ignore the Privacy Levels and Potentially Improve Performance**. Please note this could potentially expose sensitive data. - **Mac OS:** 1. Go to *Options > Project > Privacy* 2. Activate **Allow combining data from multiple sources** 5. Right-click on ***getToken*** to use the advanced editor. 6. Replace the placeholders with your **clientID** and **secret**. 7. In **getAnalysisData***,* right click to get the advanced editor. 8. Paste your JSON into queryAsJson = "< your JSON >". \| Depending on the Excel version, you may see hint boxes or a loading hint. In most cases, this does not affect the functionality, so you can simply continue. \| \| --- \| 9. Click **Close and Load.** | | Depending on the Excel version, you may see hint boxes or a loading hint. In most cases, this does not affect the functionality, so you can simply continue. | | 5 | Excel file update | Click *Data > Refresh All* to update data. - In our example file, a new token is automatically requested every time you refresh data. You'll need a new token to refresh the data only if you use a different file. \| Because your ID and Secret are saved in the file, be careful about sharing it directly with others. \| \| --- \| | | Because your ID and Secret are saved in the file, be careful about sharing it directly with others. | ## Additional information > [!NOTE] > Please be sure that you have the correct upper and lower limits set. When you copy the JSON from Mapp Intelligence, the upper limit will stay the same as it is onsite. If you would like to increase it, you can either change it before you copy the JSON or after in the JSON itself. Mapp can produce results with 5 million rows, but limits may apply depending on your consuming system (e.g. Excel). ## Automated Import 1. In Excel, open your file. For an example, please see this file [](https://cdn.document360.io/554b9b98-6720-4d8b-9919-c7b203d72648/Images/Documentation/JSON_API_powerBI_demo_version1_5.xlsx)JSON_API_powerBI_demo_version1_528.66 KB[](https://cdn.document360.io/554b9b98-6720-4d8b-9919-c7b203d72648/Images/Documentation/JSON_API_powerBI_demo_version1_5.xlsx) > Please note, this file is only an example and does not cover all potential scenarios and uses. 2. Select **Power Query editor.** ![](https://cdn.document360.io/554b9b98-6720-4d8b-9919-c7b203d72648/Images/Documentation/abfded71-f654-4414-b93b-2f94badb9e35.png) 3. In **getToken** replace <> placeholders with values - clientiD="", - clientSecret="", 4. In **getAnalysisData** 1. Paste your JSON into **queryAsJson** = "". 2. Please be sure that you check and update **noOfDimensionColumns** to make sure it matches the number of dimension columns you will get from your results. 3. Execute ![](https://cdn.document360.io/554b9b98-6720-4d8b-9919-c7b203d72648/Images/Documentation/8d46dfd7-ed0f-46ba-81fd-0af82e5148a2.png) > If you receive an error that Authentication is not specified, go to *data source settings* and configure the URLs shown to use **anonymous.** Since PowerBi does not allow data sources to be called from a URL that was transmitted by default, you have to activate **ignore privacy settings***.* > > 1. *getData* > *Query Options* > *Privacy* (global and current workbook) > Select **Ignore Privacy Levels**… > 2. Alternatively: > 1. Change the script to use fixed status and result URLs. > 2. Add the correlationID to the statusURL and the calculationID to the resultURL which you received in the response. ## Manual Data Transformation First, you will need to complete your authentication, send a JSON query, and receive your results. for example: ![](https://cdn.document360.io/554b9b98-6720-4d8b-9919-c7b203d72648/Images/Documentation/4a20f8eb-d86c-44c7-9edf-236438931c1d.png) Then you can follow these steps to transform your results into a table. 1. How to set up headers: 1. Right-click **List** in the **header** row and set it as a new query. 2. Convert to a table. 3. Click the expand icon. 4. Uncheck keep original names as a prefix. 5. Delete columns that should not be used as a header. 6. Transpose table. 7. Select the API connection that you configured in the Credential Store from the drop-down list under **Credential Store Record**. 8. Select the **GET** **Method** and **UTF-8** encoding. 2. How to set up rows 1. Right-click **List** in the **row** and set it as a new query. 2. Convert to a table. 3. Add a custom column and replace the given formula with: ```python List.First([Column1]) ``` CODE 4. Add another custom column, replacing the formula with: ```python List.First(List.Skip([Column1],1)) ``` CODE 5. Continue for every available column in your results, increasing the column number accordingly. 6. Remove the first column with the values "List". 7. Define value replacement null to 0 as needed. 8. Check your results. 3. Click **close and load**. 4. Copy your header and row query into a single sheet. 5. Now you should be able to view your table with the data. > Please note that onsite, if there are no values returned, you will see a 0. However, When using an API this will return as null. ## Related [Excel specifications and limits](https://support.microsoft.com/en-us/office/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3)