Do you know that we can connect Azure services using Excel? I came across a scenario where I was supposed to utilize Azure Blob and Azure SQL service, and bring the data to Excel and do some analysis and create charts.
In this post, we are going to connect Azure Blob Storage and Azure SQL services from Excel.
Excel Connect Options
These option are available under Data tab > Get Data > From Azure Option. Below are the supported services.
Now, we need Azure Storage Account<sauniveral> and Azure SQL service <assdemouniversal> with the AdventureSales an Azure subscription, which I have already created under the resource group <rgpostdemo>.
Connect Blob Storage
Let’s connect the blob storage by following option for <From Azure Blob Storage> and enter the Storage Account Name and Account Keys.
At this moment, you should have connected to the storage account and should be able to see the available blobs. Here, we have used the .CSV file which has been generated as an outcome of the post Cost Management – How to Automate Cost Analysis Reports in Azure.
If you want to do further data processing, then you can use Load or Transform Data. A Transform Data snapshot has shown below.
Connect Azure SQL
Let’s connect the Azure SQL by following the option for <From Azure SQL Database> and enter the SQL Server Name and Account Keys. We are going to use the sample database, which is available for the Azure SQL instance. An example of creating Azure SQL Server with a sample database is here.
Click on connect to see if the connection has succeeded. Once connect has been established, then an object’s navigator windows would get open.
Once data is available in Excel document using Power Query Editor, data analysis, reporting, and charting, etc. can be done.
So, we have seen that how can we connect two Azure services from Excel and directly use data from Azure Services. Similar way, we can practice other Azure Services as mentioned and use the data inside excel.
Hope, this would help when you come across similar scenario.