Posts

Copying files from multiple SharePoint sites onto Azure Data Lake (Gen 2) using Azure Logic Apps and Azure Data Factory

Image
An interesting area I was reading up on recently was to Ingest data from a SharePoint folder to Azure Data Lake (Gen 2). There were quite a few interesting articles on this topic, some of which are listed below, Source: MSSQL tips https://www.mssqltips.com/sqlservertip/5893/transfer-files-from-sharepoint-to-blob-storage-with-azure-logic-apps/ Source: The Tech Platform https://www.thetechplatform.com/post/copy-files-from-sharepoint-to-an-azure-blob-storage-using-microsoft-flow Like these articles suggests, I too looked at the approach of using Logic Apps to do the same. However, I had multiple SharePoint sites and folders to ingest data from. The process was too tedious to be done manually and hence a solution through which this could be semi-automated was required. The solution is broken into 2 parts, Azure Logic App – Holds the steps to read files on a particular SharePoint folder and writes it into Azure Data Lake Storage (Gen 2). Azure Data Factory – Automates the process b
3 comments
Read more

Power BI - Grouping a table and including a column with dynamic comma separated values instead of splitting rows

Image
  Hey Guys,  Recently I came across a requirement that needed a table with an unpivoted structure to be transformed in such away that it is grouped by a single column and the second column holds comma separated values of the corresponding column instead of it being split into rows (Example below). Original Table   Resultant Table I tried different approaches that included pivoting the table, replacing values with the required name, merging the columns, and replacing "0". It worked! But as all things are, its just not that simple. The requirement also required that additional batches could also be added to an existing fruit later. That meant it had to be dynamic. The search continued. I stumbled upon a similar requirement on the Power BI forum that suggested using the Table.Group along with Table.Combine functions using M Query. I incorporated it to the solution, and it worked like magic.  I followed the steps below on Power Query Editor, Group table by Fruit ->
1 comment
Read more

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
3 comments
Read more

Ingesting INSIGHTLY CRM Data onto Azure Data Lake

Image
Sup Folks! On my last blog I spoke about   Integrating data onto Power BI from INSIGHTLY CRM  (link below) https://nilhanperera.blogspot.com/2019/10/integrating-data-onto-power-bi-from.html On today's blog, lets have a look at Using Azure Data Factory (ADF) to Copy Data from INSIGHTLY CRM onto Azure Data Lake (ADL). Much like the previous post we need to use the API provided by Insightly to get access to data. Let's have a look at the steps needed to do this Pre-requisites 1. Access to INSIGHTLY. You can sign up for a Free 14 Day Trial. 2. Subscription to Azure Data Factory  3. Azure Data Lake Storage 4. Azure Storage Explorer Please have a look at my previous post to get an idea on how to obtain the API key and Request URL. Steps 1. Test the Request URL using Postman ( Chrome Extension Given Here ) Select the GET Method and paste the request URL  Select Basic Authentication from the drop down list Insert the API Key
3 comments
Read more

Integrating data onto Power BI from INSIGHTLY CRM

Image
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, https://api.insightly.com/v3.1/Help#!/Overview/Technical_Details and I came across the following article that showed how to directly connect the REST API to Power BI, https://dev.applicationinsights.io/documentation/Using-the-API/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 se
Post a Comment
Read more