Using Sesame Data with PowerBi

This guide will show you how to use PowerBi to fetch Holdings data from the Sesame Data API.

We will begin by fetching the URL from Sesame. In your Sesame Data Experience, navigate to ‘Endpoints’ and then the endpoint you would like to fetch data from. Here we will be using the Holdings endpoint. Select ‘Try it’. Choose the ‘entity’ you will be fetching data for. If you need to filter for Look through results, select ‘Look through’. Finally, copy the base URL.

After opening the PowerBi desktop application, click on ‘Get data’ and then select ‘Web’

Paste the Sesame Data URL from (1) and then click ‘OK’

When the authorization window pops up, select ‘Basic’, type in your Username and Password. You will get these from the ‘API Tokens’ menu in Sesame - read more about this here. If you see a section allowing you to 'Select which level to apply these settings to', you can leave it as the default (https://data.api.landytech.net/).

Note: If you have already configured an API source from your PowerBi application, these details will be retained by the application. If you want to update them later, you can navigate to ‘File’ > ‘Options and settings’ > ‘Data source settings’.

After adding your username and password, select ‘Save’ or 'Connect'.

PowerBi will arrive at a screen similar as below.

Before continuing, delete the ‘Applied Steps’ other than ‘Source’:

Afterwards, you will see this table, with only 'Source' remaining as an applied step:

Next, right-click in the cell next to ‘totalPages’ and then select ‘Add as New Query’:

Next, click on ‘Advanced Editor’ at the top:

Then change the query to produce a list of pages by replacing line 3 by in this text:

totalPages1 = {0..Source[totalPages] -1}

then click ‘Done’.

Right click on ‘List’ and then select ‘To Table’:

Click ‘OK’ on the popup.

Select the original table and click on ‘List’. This will open a new table (or list):

Then right-click on ‘List’ and then select the ‘To Table’ option and click ‘OK’ on the popup:

Click on Expand Columns icon:

Deselect ‘Use original column value as pre-fix’, and click ‘Load more’

Select OK

Next, click on ‘Advanced Editor’ (in the ‘Home’ tab)

Add the following code as the first line: (page as text) =>

And, in the ‘Source’ line, after the entityId, remove the closing quote, and replace it with:

?pageNumber="&page

The click ‘Done’

To ease readability, rename the function to getPages

Do this by right-clicking, ‘Rename’ on the item itself

Navigate back to your table in the left hand navigation, then on ‘ABC/123’ icon, select ‘Text’ to change the column type to Text:

In the ‘Add Column’ tab, select ‘Invoke Custom Function’:

Select the function (‘getPages’) you created previously.

If not already selected, under ‘Page’, select ‘Column Name’. Then select Column1 from the right hand menu.

Finally, click ‘OK’

PowerBi will now begin fetching your data from Sesame. If there are many records, and therefore many pages, this will take a bit of time.

When the data fetching is complete, click ‘Expand Columns’:

Select ‘Load more’ before selecting ‘OK’

Now, you can rename any columns that are being imported, or remove any if required. You will observe that each column appears twice. Please ensure that you rename or remove both values if choosing to do so.

As ‘accountCode’ is a legacy field, and so we can remove it if desired, as seen below.