Nisarg Upadhyay
Data loaded in the Power Query editor

How to access data from Azure Blob Storage using Power BI

March 17, 2020 by

In this article, I am going to explain how we can access the data from the Azure Blob Storage using Power BI. To do that, we are going to use the below tools:

  1. Azure BLOB Storage account: We have uploaded the source CSV file to it
  2. Power Query editor: To choose the data that we want to load in the Power BI report
  3. Power BI Desktop: To view the data imported from the CSV file

Demo Setup

To demonstrate the process, I have created a Blob Storage account on Azure named “myfirstblobstorage”:

Azure Storage Account details

I have also created a container named “excelfiles” in the Azure Blob Storage account. I have uploaded a CSV (comma separated values) file named “Countryregion.csv” on the Blob Storage container. To know the process of uploading the file on Azure Blob Container, read this article.

Azure Storage container

The CSV file contains the list of countries, their country codes, and create date of the record. We are going to use access keys to authenticate to the blob storage account. You can read more about accessing the blob data using the Azure portal from this article.

I am going to display the data of the CSV file in the Power BI, so I have downloaded and installed the Power BI Desktop app on my workstation. You can download and install Power BI Desktop from this location.

Connect to Azure Blob Storage

First, To import the data from the CSV file and load it into the Power BI report, we must configure the connection between Power BI and CSV file that is in Azure Blob container. To do that, first, open Power BI Desktop and click on “Get Data” from the Home ribbon:

PowerBI Desktop

In the “Get Data” dialog box, click on Azure Select “Azure Blob Storage” and click on “Connect”:

Get Data dialog box

In Azure Blob storage dialog box, provide the name of Azure storage Account or the URL of the Azure storage account and click on “OK”:

Provide Blob Storage account name

Now to connect to the Azure blob from the Azure portal, we must provide an account access key. You can find this key on the “Access keys” page of the Azure blob storage account. To obtain the access key, open the home page of Azure Portal Select Azure Blob storage account (myfirstblobstorage) select “Access keys”:

Provide access key

Copy the first key and paste it in the account key page of Power BI and click on connect.

In the navigator dialog box, you can see the list of the storage accounts and the blob containers. As I mentioned, I have created a container named “excelfiles” in the “myfirstblobstorage” blob storage account. On the left pan, you can see the list of the storage accounts and the containers. In the tight pan, you can see the list of files that have been uploaded to the selected container. As I have mentioned above, we are going to import the data of the “Countryregion.csv” file in Power BI hence select the “Countryregion.csv” file. Select the “Countryregion.csv” file and click on the Transform Data button:

Navigator dialog box

Format the output of the Power Query Editor

When you click on the “Transform Data” button, the Power Query editor window opens. In Power Query Editor, you can see the properties of “Countryregion.csv” file like Name, extension, Date accessed, date modified, date created, and folder path in tabular format. But we want to populate the content of the CSV file, so to import the data of CSV file, click on the “Combine Files” button. It is in the content column the grid view of the query editor window:

Properties of the CSV file

A dialog box named “combine files” opens. You can see the preview of the data within the CSV file. Click on OK:

Review the output of the Combine files

The data will be loaded in the Power Query editor. See the following image:

Data loaded in the Power Query editor

First, rename the headers of the columns for better understanding. To do that, double-click on the header and provide the desired name of the column:

Rename the columns

We want to load country code and country name in the Power BI report, so we must select those two columns only. In the power query editor window, we have an option to choose the specific columns. To do that, click on “Choose columns”:

Choose columns

We need the name of the country and country code; therefore, in Choose column dialog box, select “Country code” and “Country Name” columns and click OK:

Choose columns dialog box

Click OK to close the dialog box:

Load data in the Power BI report

Once the required data has been extracted from the CSV file, let us load it in the Power BI report. To do that, click on the “Close & Apply” button in the Home ribbon.

Load data in the Power BI report

The data will be loaded in the Power BI report. See the following image:

Data is loaded in the Power BI report.

Summary

In this article, I have explained how we can access the CSV file that is uploaded to the Azure Blob Storage account using Power BI. Sometimes, the organization does not use SQL Server Integration Services (SSIS) or SQL Server Reporting Services (SSRS) to create interactive reports. This article is useful for readers who are using Azure Blob Storage to store their excel files and use it to create Power BI reports.

Nisarg Upadhyay
Azure, PowerBI, SQL Azure

About Nisarg Upadhyay

Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional who has more than 8 years of experience with SQL Server administration and 2 years with Oracle 10g database administration. He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on nisargupadhyay87@outlook.com

168 Views