azure blob_如何使用Power BI从Azure Blob存储访问数据

azure blob

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:

在本文中，我将解释如何使用Power BI从Azure Blob存储访问数据。为此，我们将使用以下工具：

Azure BLOB Storage account: We have uploaded the source CSV file to it
Azure BLOB存储帐户 ：我们已将源CSV文件上传到该帐户
Power Query editor: To choose the data that we want to load in the Power BI report Power Query编辑器：选择我们要在Power BI报表中加载的数据
Power BI Desktop: To view the data imported from the CSV file Power BI Desktop：查看从CSV文件导入的数据

演示设置 (Demo Setup )

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

为了演示该过程，我在Azure上创建了一个名为“ myfirstblobstorage ”的Blob存储帐户：

azure blob_如何使用Power BI从Azure Blob存储访问数据

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 Blob存储帐户中创建了一个名为“ excelfiles ”的容器。我已经在Blob存储容器中上传了一个名为“ Countryregion.csv ”的CSV（逗号分隔值）文件。要知道上传到Azure上的Blob容器中的文件的过程中，阅读此文章。

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.

CSV文件包含国家 / 地区 列表，国家 / 地区代码以及记录的创建日期 。我们将使用访问**对Blob存储帐户进行身份验证。您可以从本文中了解有关使用Azure门户访问Blob数据的更多信息。

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.

我将在Power BI中显示CSV文件的数据，因此我已经在工作站上下载并安装了Power BI Desktop应用程序。您可以下载并安装由电力BI桌面这个位置。

连接到Azure Blob存储 (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:

首先，要从CSV文件导入数据并将其加载到Power BI报表中，我们必须配置Power BI和Azure Blob容器中的CSV文件之间的连接。为此，首先，打开Power BI Desktop，然后从Home功能区中单击“ Get Data ”：

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

在“ 获取数据 ”对话框中，单击“ Azure ”，选择“ Azure Blob存储 ”，然后单击“ 连接 ”：

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”:

在“ Azure Blob存储”对话框中，提供Azure存储帐户的名称或Azure存储帐户的URL ，然后单击“ 确定 ”：

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”:

现在要从Azure门户连接到Azure blob，我们必须提供一个帐户访问** 。您可以在Azure blob存储帐户的“ 访问** ”页面上找到此**。若要获取访问**，请打开Azure门户的主页。选择“ Azure Blob存储帐户 （ myfirstblobstorage ）”，然后选择“ 访问** ”：

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

复制第一个**并将其粘贴到Power BI的帐户**页面中，然后单击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:

在导航器对话框中，您可以看到存储帐户和Blob容器的列表。如前所述，我在“ myfirstblobstorage ” Blob存储帐户中创建了一个名为“ excelfiles ”的容器。在左窗格中，您可以看到存储帐户和容器的列表。在紧密的锅中，您可以看到已上传到所选容器的文件列表。如上所述，我们将在Power BI中导入“ Countryregion.csv ”文件的数据，因此选择“ Countryregion.csv ”文件。选择“ Countryregion.csv ”文件，然后单击“ 转换数据”按钮：

格式化电源查询编辑器的输出 (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:

当您单击“ 转换数据 ”按钮时， Power Query编辑器窗口打开。在Power Query Editor中 ，您可以以表格格式查看“ Countryregion.csv ”文件的属性，如名称， 扩展名 ，访问 日期，修改 日期，创建日期和文件夹路径 。但是我们要填充CSV文件的内容，因此要导入CSV文件的数据，请单击“ 合并文件 ”按钮。它在内容列中的查询编辑器窗口的网格视图中：

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

将打开一个名为“ 合并文件 ”的对话框。您可以在CSV文件中查看数据预览。点击确定：

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

数据将被加载到Power Query编辑器中。见下图：

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:

首先，重命名各列的标题以更好地理解。为此，双击标题并提供所需的列名称：

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”:

我们要在Power BI报表中加载国家/地区代码和国家/地区名称 ，因此我们必须仅选择这两列。在超级查询编辑器窗口中，我们有一个选择特定列的选项。为此，请点击“ 选择列 ”：

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:

我们需要国家名称和国家代码；因此，在“选择列”对话框中，选择“ 国家/地区代码 ”和“ 国家/地区名称 ”列，然后单击确定：

Click OK to close the dialog box:

单击“ 确定”关闭对话框：

在Power BI报表中加载数据 ( 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.

从CSV文件中提取所需的数据后，让我们将其加载到Power BI报告中。为此，请单击主页功能区中的“ 关闭并应用 ”按钮。

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

数据将被加载到Power BI报告中。见下图：

摘要 (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.

在本文中，我已说明了如何使用Power BI访问上传到Azure Blob存储帐户的CSV文件。有时，组织不使用SQL Server集成服务（SSIS）或SQL Server报告服务（SSRS）来创建交互式报告。本文对于使用Azure Blob存储来存储其excel文件并用于创建Power BI报表的读者很有用。

翻译自: https://www.sqlshack.com/how-to-access-data-from-azure-blob-storage-using-power-bi/