Analyze data in Data Lake Store by using Power BI
In this article you will learn how to use Power BI Desktop to analyze and visualize data stored in Azure Data Lake Store.
Before you begin this tutorial, you must have the following:
- An Azure subscription. See Get Azure free trial.
- Azure Data Lake Store account. Follow the instructions at Get started with Azure Data Lake Store using the Azure Portal. This article assumes that you have already created a Data Lake Store account, called mybidatalakestore, and uploaded a sample data file (Drivers.txt) to it. This sample file is available for download from Azure Data Lake Git Repository.
- Power BI Desktop. You can download this from Microsoft Download Center.
Create a report in Power BI Desktop
- Launch Power BI Desktop on your computer.
From the Home ribbon, click Get Data, and then click More. In the Get Data dialog box, click Azure, click Azure Data Lake Store, and then click Connect.
- If you see a dialog box about the connector being in a development phase, opt to continue.
In the Microsoft Azure Data Lake Store dialog box, provide the URL to your Data Lake Store account, and then click OK.
In the next dialog box, click Sign in to sign into Data Lake Store account. You will be redirected to your organization's sign in page. Follow the prompts to sign into the account.
After you have successfully signed in, click Connect.
The next dialog box shows the file that you uploaded to your Data Lake Store account. Verify the info and then click Load.
After the data has been successfully loaded into Power BI, you will see the following fields in the Fields tab.
However, to visualize and analyze the data, we prefer the data to be available per the following fields
In the next steps, we will update the query to convert the imported data in the desired format.
From the Home ribbon, click Edit Queries.
In the Query Editor, under the Content column, click Binary.
You will see a file icon, that represents the Drivers.txt file that you uploaded. Right-click the file, and click CSV.
You should see an output as shown below. Your data is now available in a format that you can use to create visualizations.
From the Home ribbon, click Close and Apply, and then click Close and Apply.
Once the query is updated, the Fields tab will show the new fields available for visualization.
Let us create a pie chart to represent the drivers in each city for a given country. To do so, make the following selections.
From the Visualizations tab, click the symbol for a pie chart.
The columns that we are going to use are Column 4 (name of the city) and Column 7 (name of the country). Drag these columns from Fields tab to Visualizations tab as shown below.
The pie chart should now resemble like the one shown below.
By selecting a specific country from the page level filters, you can now see the number of drivers in each city of the selected country. For example, under the Visualizations tab, under Page level filters, select Brazil.
The pie chart is automatically updated to display the drivers in the cities of Brazil.
- From the File menu, click Save to save the visualization as a Power BI Desktop file.
Publish report to Power BI service
Once you have created the visualizations in Power BI Desktop, you can share it with others by publishing it to the Power BI service. For instructions on how to do that, see Publish from Power BI Desktop.