Excel Integration

Note

The below screenhsots are taken using MS Office 365. If you are using Office 365 or MS office 2019, you will be able to follow along.


Open Excel πŸ‘©πŸ»β€πŸ’»πŸ§‘πŸ»β€πŸ’» and Go to Data Ribbon > Get Data > Launch Power Query Editor

dataribbon

In Power Query Editor > Home Tab > Manage Parameter > New Parameter

powerribbon

Copy your apiKey from Jamku, we will need this in the next step

keycopy

In the Parameter Dialogue Box, enter the below details

  • Name: apiKey
  • Description: Can leave it blank
  • Required: Tick
  • Type: Any
  • Current Value: apiKey which you copied from Jamku

Click ok to save the Parameter

parameter

Go to Home Tab > Data Rource yyyy

In the From Web Dialogue Box, which opens, click on Advanced & Enter the values as shown

  • Url Part 1: https://api.jamku.app/integration/client/v1/ (don't forget the trailing /)
  • Url Part 2: First Select Parameter in Drop Down & Then Select the Parameter(i.e. apiKey) which we created in the previous step.

Ensure the URL Preview is same as shown belowπŸ‘‡πŸ». Click ok, to add the data source.

dataribbon

If you are using the latest version of Excel / Power query, then you will be shown a 2nd Dialoge, as shown below. Simply, click Connect to being connection

dataribbon

You will see a processing screen as shown below

dataribbon

Once the processing is completed you will see multiple Record with title List If You don't see many records, then something went wrong with your setup. If you are unable to solve the error yourself, please get in touch with Jamku Support team for help.

dataribbon

πŸ‘πŸ»Congratulate yourself on getting the data in Excel from Jamku. Now, let's start the data transformation.

Right Click on List and Click on To Table

dataribbon

In the To Table Dialogue, click Ok.

dataribbon

Now you will see a small Expansion button in besides Column1. Click on this button.
In below screenshot, the mouse is at this Expansion button

dataribbon

You will be shown all the field names Excel could Extract.
Don't unselect any columns, here. In the next step we will do it in the recommended way.

Un Check Use Original Column Names as Prefix and click OK.

dataribbon

You will now see all the clients details in the table. Let's select which columns you would like to keep / hide On the Home Tab > Click Choose Columns > Choose Columns

dataribbon

Select the columns which you wish to have it in your report

dataribbon

You can also filter only the clients which are active. (i.e. Filter out deactivated clients)

dataribbon

Whatever steps you perform are shown in Applied Steps panel in the right side of the table.
Here, you can also change the connector name. We have renamed it to client.

dataribbon

Once you finish all the data transformation, you can click on Close & Load.
It will close the Power Query Editor & take you back to Excel.

dataribbon

You have successfully loaded the data from Jamku to Excel.

dataribbon

The connection to Jamku is stored in the file.

dataribbon

If there is any data changes in Jamku, all you need to do is click on Refresh button in Query Ribbon

dataribbon

Last Updated: