Power BI Desktop中的查询编辑器入门

Power BI is a data analytics tool developed by Microsoft used to visualize data and find useful insights. In this article, you will see how to work with the Query Editor in Power BI desktop. Power BI comes in various versions, i.e., Power BI Desktop, Power BI Service, Power BI Mobile, and Power BI Developer. Power BI desktop is the free version, and the query editor is available in all three versions.

Power BI是Microsoft开发的一种数据分析工具，用于可视化数据并找到有用的见解。 在本文中，您将看到如何在Power BI桌面中使用查询编辑器。 Power BI有各种版本，例如Power BI桌面，Power BI服务，Power BI Mobile和Power BI Developer。 Power BI桌面是免费版本，查询编辑器在所有三个版本中都可用。

Power BI Desktop has several components to transform, model, and visualize data and also generate reports from data. The Query Editor in Power BI is used to transform or edit data files before they are actually loaded into the Power BI.

Power BI Desktop具有多个用于转换，建模和可视化数据以及从数据生成报告的组件。 Power BI中的查询编辑器用于在实际将数据文件加载到Power BI中之前对其进行转换或编辑。

The Query Editor plays the role of an intermediate data container where you can modify data by selecting rows and columns, splitting rows and columns, pivoting and unpivoting columns, etc. The changes made by the Query Editor in Power BI are not reflected in the actual dataset. Once you have pre-processed the data and have transformed it into the required format, you can load the data into the Power BI environment.

查询编辑器充当中间数据容器的角色，您可以在其中通过选择行和列，拆分行和列，旋转和取消旋转列等来修改数据。PowerBI中查询编辑器所做的更改不会反映在实际中数据集。 预处理数据并将其转换为所需格式后，即可将数据加载到Power BI环境中。

In this article, you will see how to use the Query Editor in Power BI desktop to transform data and then load it into the Power BI desktop.

在本文中，您将看到如何在Power BI桌面中使用查询编辑器来转换数据，然后将其加载到Power BI桌面中。

将Power BI连接到数据源 (Connecting Power BI to a Data Source )

Before you can reprocess data using Query Editor in Power BI, you have to connect Power BI to a data source. Power BI can take data from various data sources such as MS Excel, Text/CSV, SQL Server, Web, etc. In this article, you will see how to connect to a data source located on the internet.

必须先将Power BI连接到数据源，然后才能使用Power BI中的查询编辑器重新处理数据。 Power BI可以从各种数据源（例如MS Excel，Text / CSV，SQL Server，Web等）中获取数据。在本文中，您将看到如何连接到Internet上的数据源。

If you are working with a SQL dataset see this article.

如果您正在使用SQL数据集， 请参阅本文 。

To do so, open Power BI Desktop and from the Sign In dialogue box shown below, select on the “Get data” button located on the left-hand side of the dialogue box.

为此，请打开Power BI Desktop，然后从如下所示的“登录”对话框中，选择位于对话框左侧的“获取数据”按钮。

A window will appear listing all the data sources for Power BI. Type “web” in the search box. You will see the “Web” data source appear in the All data source list. Select “Web” and click the “connect” button, as shown in the following screenshot:

将出现一个窗口，列出Power BI的所有数据源。 在搜索框中输入“ web”。 您将看到“ Web”数据源出现在“ 所有数据源”列表中。 选择“ Web”并单击“连接”按钮，如以下屏幕截图所示：

You will see the following dialogue box. In the URL field, enter the following URL:

您将看到以下对话框。 在URL字段中，输入以下URL：

https://raw.githubusercontent.com/datasciencedojo/datasets/master/titanic.csv

https://raw.githubusercontent.com/datasciencedojo/datasets/master/titanic.csv

This URL points to a CSV file located at Github. The file contains the Titanic dataset, which contains information about the passengers who traveled on the unfortunate ship Titanic that sank in 1912.

该URL指向位于Github的CSV文件。 该文件包含Titanic数据集，其中包含有关1912年沉没的不幸船Titanic上旅行的乘客的信息。

Power BI might take some time to establish a connection with the online data source depending upon the size of the file. Once the connection is established, you should see the following window:

Power BI可能需要一些时间来建立与在线数据源的连接，具体取决于文件的大小。 建立连接后，您将看到以下窗口：

At this point in time, if you click the “Load Button”, the dataset will be loaded into Power BI. If you want to open the data in the Query Editor in Power BI and perform some pre-processing, you need to click the “Transform Data” button.

此时，如果单击“加载按钮”，则数据集将被加载到Power BI中。 如果要在Power BI的查询编辑器中打开数据并执行一些预处理，则需要单击“转换数据”按钮。

Data opened in the Query Window looks like this:

在查询窗口中打开的数据如下所示：

You can see all the columns in your dataset along with the column type. For instance, the Name column has type ABC, which means the column contains the text. You can actually click on the column type to see all the column types and their names as shown in the following screenshot:

您可以看到数据集中的所有列以及列类型。 例如，“名称”列的类型为ABC，这意味着该列包含文本。 您实际上可以单击列类型以查看所有列类型及其名称，如以下屏幕截图所示：

使用查询编辑器 (Working with Query Editor )

Now that we have opened the data in the Query Editor in Power BI, we can apply several pre-processing functions on the data. Let’s see some of these functions:

现在我们已经在Power BI的查询编辑器中打开了数据，我们可以在数据上应用几个预处理功能。 让我们看看其中一些功能：

删除和过滤行 (Removing and filtering rows)

Query editor allows you to remove or filter the rows from a dataset.

查询编辑器允许您从数据集中删除或过滤行。

删除具有空值的行 (Removing rows with Null values)

If you look at the Titanic dataset in the Query Editor, you can see that the Age column contains some null values as shown below:

如果在查询编辑器中查看Titanic数据集，则可以看到Age列包含一些空值，如下所示：

You can remove the rows where Age contains null values. To do so, click on the button containing the inverted triangle at the extreme right of the Age column. And then uncheck the “null” from the list of all the unique values in the age column as shown below:

您可以删除“年龄”包含空值的行。 为此，请单击“年龄”（Age）列最右端的包含倒三角形的按钮。 然后从年龄列中所有唯一值的列表中取消选中“ null”，如下所示：

Click the OK button, and you will see that the rows where the Age column contains null values have been removed.

单击确定按钮，您将看到“年龄”列包含空值的行已被删除。

删除具有空白值的行 (Removing rows with blank values)

Similarly, you can repeat the same procedure to remove the rows where the Cabin column contains blank rows, as shown below:

同样，您可以重复相同的过程以删除“ Cabin”列包含空白行的行，如下所示：

You have to again open the list of unique values for the Cabin column and then uncheck the checkbox to the left of the “blank” option.

您必须再次打开“机舱”列的唯一值列表，然后取消选中“空白”选项左侧的复选框。

Click the OK button and you will see that all the rows where the Cabin column contains empty values will be removed.

单击确定按钮，您将看到“机舱”列包含空值的所有行将被删除。

You can also undo the operations that you perform on your dataset in Query Editor in Power BI. In the “APPLIED STEPS” window in the following screenshot, you can see the operations that you have performed on the dataset. You can remove the operations by clicking the cross icon to the left of each step.

您也可以在Power BI的查询编辑器中撤消对数据集执行的操作。 在以下屏幕截图的“ APPLIED STEPS”窗口中，您可以看到对数据集执行的操作。 您可以通过单击每个步骤左侧的叉形图标来删除操作。

根据条件过滤行 (Filtering rows based on condition)

You can also remove rows based on values in a particular column. For instance, if you want to only keep the rows where the Fare column has values either greater than 40 or less than 10, you can use the Number Filter property. To do so, click on the triangle on the left of the Fare column name and then click on the “Number Filters” property. A dropdown list will pop up, select “Greater Than” from the list as shown below:

您还可以根据特定列中的值删除行。 例如，如果只想保留“票价”列的值大于40或小于10的行，则可以使用“数字过滤器”属性。 为此，请单击“票价”列名称左侧的三角形，然后单击“数字过滤器”属性。 将会弹出一个下拉列表，从列表中选择“ Greater Than”，如下所示：

You should see the following window. Add 40 and 10 in the corresponding fields and click OK button. You will see that the dataset will now contain only the rows where the Fare column has a value greater than 40 or less than 10.

您应该看到以下窗口。 在相应的字段中添加40和10，然后单击“确定”按钮。 您将看到数据集现在仅包含“票价”列的值大于40或小于10的行。

You can also apply filters on the text column. For instance, if you only want to keep the records of the Female passengers, uncheck “male” checkbox from the Sex column filters, as shown below.

您也可以在文本列上应用过滤器。 例如，如果您只想保留女性乘客的记录，请取消选中“性别”列过滤器中的“男性”复选框，如下所示。

移除列 (Removing columns)

Like rows, you can also remove columns that you do not want in your dataset. For instance, the Cabin column in the Titanic dataset contains many null values. We can remove it. To do so, click the “Choose Column -> Choose Columns” option from the top menu, as shown below.

像行一样，您也可以删除数据集中不需要的列。 例如，泰坦尼克号数据集中的Cabin列包含许多空值。 我们可以将其删除。 为此，请从顶部菜单中单击“选择列->选择列”选项，如下所示。

You will see a list of all the columns in your dataset. Uncheck the checkbox to the left of the column that you want to delete as shown below:

您将看到数据集中所有列的列表。 取消选中要删除的列左侧的复选框，如下所示：

Click the “OK” button and you will see that the Cabin column has been removed from the dataset.

单击“确定”按钮，您将看到“机舱”列已从数据集中删除。

Another way to remove a column is by simply selecting the column and then clicking the “Remove Columns -> Remove Columns” option from the top menu, as shown in the following two screenshots:

删除列的另一种方法是，只需选择该列，然后从顶部菜单中单击“删除列->删除列”选项，如以下两个屏幕截图所示：

You can also rename a column. To do so, simply double click the column header and write the new name of the column. For instance, in the following screenshot, the Fare column has been renamed as Ticket Price.

您也可以重命名列。 为此，只需双击列标题并输入该列的新名称。 例如，在以下屏幕截图中，“票价”列已重命名为“票价”。

替换值 (Replacing values)

You can replace values in a particular column via the Query Editor in Power BI. For instance, to replace the blank rows in the Cabin column containing a null value, you first need to select the Cabin column and then click the Replace Value option from the top menu as shown below:

您可以通过Power BI中的查询编辑器替换特定列中的值。 例如，要替换Cabin列中包含空值的空白行，您首先需要选择Cabin列，然后从顶部菜单中单击Replace Value选项，如下所示：

You will see the following window. Here you can specify the original value and the new value in the “Value To Find”, and “Replace With” fields, respectively.

您将看到以下窗口。 在这里，您可以分别在“要查找的值”和“替换为”字段中指定原始值和新值。

Here is a screenshot of the output:

这是输出的屏幕截图：

You can see that blank values have been replaced by null values.

您可以看到空白值已替换为空值。

拆分列 (Splitting columns)

You can split one column into more using a number of different options.

您可以使用许多不同的选项将一列分成更多列。

For example, if you want to split the Ticket column into two columns where the first new column contains the first 2 characters of the original column and the second new column contains the remaining characters from the original column, you need to select the Ticket column and then click “Split Column -> By Number of Characters” option from the top menu as shown below:

例如，如果要将“票证”列拆分为两列，其中第一新列包含原始列的前2个字符，第二新列包含原始列中的其余字符，则需要选择“票证”列，然后然后从顶部菜单中单击“拆分列->按字符数”选项，如下所示：

Next, specify the number of characters after which you want to split the values in the original column, as shown below:

接下来，指定要分割原始列中的值的字符数，如下所示：

Once you split the columns, you should see two new columns, Ticket.1 and Ticket.2, as shown below:

拆分列后，应该会看到两个新列，Ticket.1和Ticket.2，如下所示：

结论 (Conclusion)

Query Editor in Power BI is a very useful tool to transform data before it is actually loaded into the Power BI environment. In this article, we covered some of the most basic Query Editor in Power BI operations. We saw how to remove and filter rows and columns, how to replace values in the dataset and how to split columns based on certain conditions.

Power BI中的查询编辑器是一个非常有用的工具，可以在将数据实际加载到Power BI环境之前对其进行转换。 在本文中，我们介绍了Power BI操作中一些最基本的查询编辑器。 我们看到了如何删除和过滤行和列，如何替换数据集中的值以及如何根据特定条件拆分列。

翻译自: https://www.sqlshack.com/getting-started-with-query-editor-in-power-bi-desktop/