Anyone using Power BI to pull 8x8 CRM data?

Part 2/2:

Create a new query which we will use to create the function

Get data using the Web connector. At the URL screen select ‘Advanced’.

Enter the URL up to the part where the group id needs to be entered: https://vcc-au1.8x8.com/api/stats/groups

At that point select the next URL part as ‘parameter’ and select the group-id parameter you created earlier
Now complete the URL by adding the final third part of the URL query /activities

Now click OK.

At the Navigator screen, select the activity table then load the query into the data model. This returns the list of activities for the group-id we manually assigned to the parameter when we created it.

Right-click your new query and select ‘Create Function’

Give it a descriptive name then click OK.

The result is a new group that contains the original query, parameter and newly created function

Create a new base query to get a list of groups – it’s this list we want to run our function over

Get data using the Web connector. At the URL screen type in: https://vcc-au1.8x8.com/api/stats/groups
then click OK.

Select the table then load that into the data model

Add a new column using our function

On the ‘Add Column’ ribbon select ‘Invoke Custom Function’.

Select your query from the Function query drop down list.
For the parameter make sure it shows the ‘column’ icon and that it has the group-id column selected then click OK.

You will now get a new column with a table as the element

Expand your new column.

The result is you now get a table of activities for each of the groups listed in the group-id column.

If a group is removed or new group added, the query will automatically pick this up and refresh accordingly.

Workaround for refreshing the data in Power BI Service

At this point the dataset can be refreshed in PBI Desktop successfully.

However it is not possible to refresh it once uploaded to the PBI Service.

The problem is due to using a parameter, which we need to do, in the Web.Contents() M query function and the code that the Desktop application generates when we compile the Get Data web URL parts.

Working through the same Group Activities example, the issue is specifically in the query in the function GetActivities we created earlier:

This can be resolved using a Web.Contents option called RelativePath.

Update the query to read as:

Now you should be able to upload the report to the PBI Service and schedule refreshes.

If this is the first time you have used the data source in the PBI Service you will also be prompted to provide login credentials, at which point you should select ‘Basic’ authentication, enter the account information and set Privacy Level to ‘Organizational’.

VCC API 50 record limit (pagination)

The API will only return 50 records at a time.

You can see this in Power Query by looking at the results our function returns (within the function, not the GroupActivities query) and you’ll see in the bottom left corner it says 50 ROWS.

To pull back more than 50 records you need to call the URL multiple times, requesting the offset record number using the URL option ‘?n=x’ where x is the offset record number.

By default, n is 0 so you will always only get the first 50 records that exist.

For our group activities function, the following 2 calls return the same first 50 records:

To get more than 50 records using the above modified function code we create a list of numbers counting in steps of 50 – which we call offset, convert that to a table, then call this modified function using the offset as an input to the function.

Create a new parameter called ‘offset’, set type to ‘Text’ and set ‘Current Value’ to 0.

Change your function query to read:

So now the query will call the parameter called ‘offset’ which is set to 0. All good.

But now we will have another problem.

Requesting a record offset that does not exist

If we call for a record offset that does not exist, 8x8 will not return a blank XML table. It only returns the ending table element which causes problems for Power BI when trying to parse the results.

For example if I manually type this URL into my web browser:

It does not include the opening XML tag “<activities>”

This results in an error when trying to parse incomplete tables.

The fix is to test if the call returns an error, and if it does then return a blank table with the opening and closing headers.

We do this using the try..otherwise error handling expression feature of M Query.

Replace the Source query above with this (changes in red):

Now to extend this to call multiple offsets.

Create a new Blank query then enter the following code:

You’ll now have a list of numbers counting from 0 in steps of 50, 500 times.

Rename this query to GroupActivitiesOffset (the expected total record count for group activities compared to other queries won’t be anywhere similar so no point using the same x thousand offsets if we are only expecting a few dozen).

Now

• disable loading of the list

• convert it to a table

• rename the column to ‘offset’

Tranform the column type to Text

Now we need to inject this list into the GroupActivities query so we can call our function against each group id and offset to pull back all records for each group.

Go to the GroupActivities query, select the ‘Changed Type’ step then add a new Custom Column. Call it offset and in the formula field type the name of the offset table we just created.

This now gives us a table showing us all groups we have with a list of offset numbers for each group.

Now expand the column.

If the stars are aligned, you should now be able to click on the last step in the query and get all records for each group. You will know this is the case if you have more than (number of groups) x 50 records returned.
Lastly you might hit a PBI service timeout limit if there's too much to pull back, in which case you could play around with parameters and the starting offset in the List.Numbers function - or even better would be to somehow determine how many records there are first, then minus however many records from that number that you know you can successfully get reliably then dynamically set the starting number in the List.Numbers function to that. For that you're on your own because I just manually bump it up every couple of months instead (got other fires to put out that are more important).