News 25 May 2020 16:34 PM

Power BI Reporting
on ZOHO CRM

Marius Oprea

We have stumbled upon this project not long ago where one of our clients was looking to use the Power BI Service to ingest data from various sources, and then create reports using Power BI. As there is no connector in Office 365 to connect to Zoho CRM, we were faced with the challenge to come up with a way to ingest data from multiple Zoho CRM modules. Searching through various blogs, we have realised there is nothing out there to guide us through step by step.

Now that we have completed the project for our client, we though it would be a good idea to share the code with all out there. Now we are not going to delve deep into how to get a client id, client secret, and refresh token from the Zoho Developer Console. You can check details about how to do that here: https://www.zoho.com/writer/help/api/v1/oauth-requests.html

A bit of advice when registering your application in the Zoho Developer Console. We have used the below as the “Authorized Redirect URIs” for the Power BI Service:
https://oauth.powerbi.com/views/oauthredirect.html

So, the logic is this:

  1. We will create a Dataflow in the Power BI Service
  2. We add a new Entity from a Blank Query
  3. We then write up a bunch of M to do the following:
    1. Request an Access Token for Zoho CRM using the OAuth 2.0 API
    2. Using the Access Token make an API call to Zoho CRM to fetch all records
    3. Filter out the empty pages from the result set

So, what we will do is explain the process and the important bits, then you can get the full Query posted at the end of the page.

Power Query to generate an Access Token for Zoho CRM

We start our little script by setting up a couple of variables and set them with some constant values as follows:

refresh_token = "1120.c72dbww23941casdass29f86fc4.0e87a488f25433208a7198a853fe0d",
client_id = "1234.AW2sdasddasSU8Q0ZsdasdAB69R",
client_secret = "93316sa23234rweq2fedb25f8f1211d9c041csdas351",
ZohoModule = "Accounts",
PageUBound = 200

A couple of things to note here:

  1. The refresh_token, client_secret, and client_id values are obviously made up. So, you will have to replace those with the actual values from your Zoho Developer Console
  2. We have setup ZohoModule as “Accounts”, but you can set that up to whatever Module you want to query
  3. The PageUBound variable needs to be setup to something much higher than whatever number of pages you expect to paginate by. A page has 200 records, so if you expect to have let’s say around 1000 records in your module and maybe 1000 new records added per year, then setting that to 100 would keep you going for a while
  4. We have generated our actual refresh_token using Postman. There is a ready to use collection for Postman here: https://www.zoho.com/writer/help/api/v1/download-postman-collection.html

We then create the URL to call the API and get the OAuth Token to connect for this session. We will later use token_url to create the API call URL and fetch data from Zoho.

So, the token_url expression looks like this:

token_url = "https://accounts.zoho.eu/oauth/v2/token?refresh_token=" & refresh_token & "&client_id=" & client_id & "&client_secret=" & client_secret & "&grant_type=refresh_token"

VERY IMPORTANT: You need to ensure that your API calls are done using the correct DC (Data Center) route. In our instance, this is in Europe, so we have used accounts.zoho.eu. But if your DC is in another region, you need to adjust the URLs to reflect the correct DC zone. More details about the available DC’s here: https://www.zoho.com/crm/developer/docs/api/multi-dc.html

Power Query paginating results

This has been a bit of a challenge, but here a few highlights and what you should look for:

  1. The Zoho API only returns 200 records per call
  2. Results are returned paginated in the form of 200 records per page
  3. At some stage we ended with the whole query done but not being able to schedule refresh. Check this blogpost around using a Query Parameter. It was a life saviour in our case:
    https://community.powerbi.com/t5/Service/Dynamic-Web-Contents-Power-BI-Refresh-Error/td-p/714526

So, we have built a recursive function using List.Generate which will add all records returned from each page to a list which we can then work with and model.

We then filter the result to remove the rows where blank pages are returned. There would be blank pages because we are setting PageUBound to a number that is higher than the actual number of pages returned from Zoho. So, we just filter out those empty rows and only keep the ones with pages that have records in them:

Base_Table = Table.SelectRows(Table.FromRecords(All_Data), each [Source1] <> null and [Source1] <> "")

The full Power Query M code below:

let
// SET THE VARIABLES WITH VALUES FROM THE Zoho Developer Console
 refresh_token = "1120.c72dbww23941casdass29f86fc4.0e87a488f25433208a7198a853fe0d",
 client_id = "1234.AW2sdasddasSU8Q0ZsdasdAB69R",
 client_secret = "93316sa23234rweq2fedb25f8f1211d9c041csdas351",
 ZohoModule = "Accounts",
 PageUBound = 200,
// SET THE CALL URL TO GET THE REFRESH TOKEN
 token_url = "https://accounts.zoho.eu/oauth/v2/token?refresh_token=" & refresh_token & "&client_id=" & client_id & "&client_secret=" & client_secret & "&grant_type=refresh_token",  body = "",
// CALL THE ZOHO CRM API USING THE AUTHENTICATION TOKEN
 Source = Json.Document(Web.Contents(token_url,
  [
    Headers = [#"Content-Type"="application/json"],
   Content=Text.ToBinary(body)
  ])),
// GET THE ACCESS TOKEN VALUE
 accessToken = Source[access_token],
// DO A RECURSIVE FUNCTION TO PAGINATE AND GET RECORDS FROM EACH PAGE
All_Data = List.Generate(
  () => [x=1, Source1 = Json.Document(Web.Contents("https://www.zohoapis.eu/crm/v2/"&ZohoModule, [Headers= [Authorization="Zoho-oauthtoken "&accessToken, #"Content-Type"="application/json"]]))],
  each [x]<=PageUBound,
  each [x=[x]+1, Source1 = if Value.Metadata(Web.Contents("https://www.zohoapis.eu/crm/v2/"&ZohoModule&"?page=",[Query=[page=Number.ToText(x)],Headers=[Authorization="Zoho-oauthtoken"&accessToken, #"Content-Type"="application/json"]]))[Response.Status] = 200
       then Json.Document(Web.Contents("https://www.zohoapis.eu/crm/v2/"&ZohoModule&"?",[Query=[page=Number.ToText(x)],Headers=[Authorization="Zoho-oauthtoken "&accessToken, #"Content-Type"="application/json"]])) else "" ]
),
// FILTER THE LIST TO REMOVE ANY PAGES THAT HAVE TO RECORDS
// THEN CONVERT THE LIST INTO A TABLE USING FromRecords
 Base_Table = Table.SelectRows(Table.FromRecords(All_Data), each [Source1] <> null and [Source1] <> "") in Base_Table

VERY IMPORTANT: If you have got the right connection details from Zoho Developer Console this will work, but the first time you create the Dataflow, a very annoying thing happens. You will be asked to specify how to connect. Because we are passing all the connection details in the Query this needs to be Anonymous. So basically, you will find yourself in a position where you click Configure Connection, select Anonymous in the popup window, then click Connect. For whatever reason, you need to do that several times before it sticks. It seems to be a glitch there, so try it out a few times until you get a warning message with a continue button. Click continue, and you are good to start modelling now.









Pwer Platform Group Limited is a Private Limited Company registred
in englad with company number 12200900.

Power Platform Group Limited is a Microsoft Partner under MPN
number 5290101