Integrating data onto Power BI from INSIGHTLY CRM

Hi Guys,
The other day I was looking at possibilities to get data from INSIGHTLY and visualize it through Power BI.
I was given the following link that lead me to the INSIGHTLY Developer portal,


and I came across the following article that showed how to directly connect the REST API to Power BI,


I followed the steps mentioned on the page associated with the link above and voila! it worked! However, the above article talks about "Displaying a time series using the Metrics API".

Let ma explain how this can be applied to get data from INSIGHTLY.
For this example, lets look at the steps required to get Project related data onto Power BI.

Pre-requisites

1. Access to INSIGHTLY. You can sign up for a Free 14 Day Trial.
2. Power BI Desktop.

Steps

1. Login to INSIGHTLY CRM > Navigate to the user menu and select User Settings.


2. On the User Settings Menu, navigate the menu on the left hand side and click on the User Setting


3. At the bottom of the User Settings Section you will find an API Key and Also generate one. (Copy that onto a notepad for future use)




5. Add the API Key onto the API Key text box and click on the arrow icon


6. Scroll down and select "Projects" on the menu on the left > Select "Gets a list of Projects" from the sub menu

7. Scroll down on the pane on the right and click on "TRY" under Test Endpoint


8. This will generate a Request URL on the dark grey pane on the right (Copy that onto a notepad for future use)



9. Open up Power BI Desktop > Select Get Data > Select Web


10. On the URL section, paste the Request URL (from step number 8) > click on OK



11. You will come to Menu that says "Access Web Content" > Select "Basic" on the menu on the left and insert the API Key (from Step 3) as the username and leave the password blank > On the "Select which level to apply this input" drop down, select the appropriate level (In our case lets apply it to projects only) > Click on OK


12. This will take you to the power query editor window



13. Click on "To Table" > This will open up a Menu. Keep the default values and click on OK




14. Once converted to a table, click on the icon and select the required columns and click on OK


15. You will now be able to view data on a tabular mode



16. You can do necessary transformations here and then "close and apply"



17. The data set will now be available for you to create a report




Like wise you can import other available data sets by selecting the relevant data you need from the INSIGHTLY developer portal and create your Power BI Data Model

Important Points to note

1. Data does not get automatically refreshed on Power BI Desktop when a new record is added. You will have to manually refresh it.
2. You may have reinsert the credentials as above on the Power BI Service when uploaded.


Hope this came in handy for those of you struggling to connect the INSIGHTLY REST API directly to Power BI!





Comments

Post a Comment

Popular posts from this blog

Power BI - Importing fbprophet and using Anaconda as Default Python

Image
Hi Guys, The other day, I was trying to use the fbprophet package to do a Time Series Analysis using Python  through Power BI and "Smack!", I hit a roadblock. I tried installing the fbprophet packeage using pip install fbprophet but it threw a ton of errors. So naturally I looked around on the internet and came across quite a number of forums that confirmed there was a problem when installing fbprophet through pip on Windows 10. One such link is https://github.com/facebook/prophet/issues/2 While I was reading through a few more articles I found out that by using the Anaconda platform it was possible to install and implement the fbprophet package and so I tried it out and after a few struggles I managed to get it to work! These are the steps I took, 1. Uninstalled the existing python instance/s on my machine 2. Deleted the existing application folders 3. Downloaded the graphical installer(Python version 3.7) for Anaconda from the website below https://www
Read more

Power BI- Route Map

Image
Hey Guys, It’s been awhile :p So, I was working on a little project where I wanted to show a proper route that was mapped correctly (much similar to google maps) on Power BI, however there was no visual supporting it at the time. As always, I looked into alternatives for this and I found a visual called route maps on the market place by Weiwei Cui. Figure 1 : Route Map - PBI Marketplace I was also able to find the guide for this visual (link below) https://weiweicui.github.io/PowerBI-Routemap/ This did not quite do the trick as it showed the displacement of the two inserted locations and not the route to it. Figure 2 : Incorrect Route (Displacement) I was getting a little annoyed this point, so I googled alternate options and I found out these things. The ability to create customs points on google maps The ability to export it in KML/KMZ Format The ability to convert it to an Excel file
Read more