【PowerBI脚本编写】：PowerQuery和PowerScript入门与进阶


参考资源链接：[PowerBI使用指南：从入门到精通](https://wenku.csdn.net/doc/6401abd8cce7214c316e9b55?spm=1055.2635.3001.10343)
# 1. PowerBI脚本编写概述

在当今数据驱动的商业世界中，能够熟练编写和应用脚本来处理数据，已成为IT行业从业者的重要技能之一。Microsoft Power BI作为一种流行的数据分析和可视化工具，其脚本编写能力在提升数据分析效率和深度方面起着关键作用。

Power BI的脚本主要分为两大类：Power Query和Power Script。Power Query是用于数据转换和清洗的过程，而Power Script则更侧重于数据分析模型的构建和报告生成。本章将对这两种脚本的编写进行基础性概述，为后面章节中更深入的讨论和实践打下坚实的基础。

## 1.1 PowerBI脚本编写的重要性
编写脚本在PowerBI中具有至关重要的作用，因为它们能够自动化日常的数据处理任务，提高工作效率，同时允许开发者创建复杂的数据分析模型。对于需要进行数据探索和报告生成的分析师和数据科学家来说，脚本编写能力是他们的核心技能之一。

## 1.2 Power Query与Power Script的区别
- **Power Query**: 它是一种用于数据获取、数据转换和数据准备的引擎。用户可以通过可视化的界面进行操作，也可以编写M语言脚本来实现复杂的数据处理逻辑。
- **Power Script**: 也称为DAX (Data Analysis Expressions)，主要用于计算和数据建模，常用于创建计算列、计算字段以及测量值。

通过本章的学习，读者将获得编写Power BI脚本的初步认识，并为后续深入掌握Power Query和Power Script的应用打下坚实基础。

# 2. PowerQuery的基础应用

在第一章中，我们介绍了Power BI脚本编写的概念和重要性。现在，我们将深入探讨Power Query，这是Power BI中用于数据提取、转换和加载（ETL）的重要工具。本章节将通过详细步骤，深入解析Power Query的基础应用，让读者能够熟练掌握数据获取和转换，高级数据处理以及性能优化的技巧。

## 2.1 PowerQuery的数据获取和转换

Power Query在数据的获取和预处理阶段扮演着关键角色。它允许用户通过多种方式获取数据，并应用一系列转换操作，以满足分析和报告的需求。

### 2.1.1 数据获取的多种方式

Power Query提供了多种数据获取方式，包括但不限于从文件、数据库、APIs、在线服务等提取数据。我们以下面的几个数据获取方式为例，进行详细介绍。

#### 从文件导入数据

Power Query可以导入多种文件格式的数据，如Excel工作簿、CSV文件、文本文件等。以下是导入Excel文件的步骤：

1. 打开Power BI Desktop，选择“主页”选项卡中的“获取数据”。
2. 在弹出的“获取数据”窗口中选择“文件”类别，然后选择“Excel”。
3. 输入或浏览Excel文件路径，并点击“确定”。
4. 选择需要导入的工作表或数据表范围，并点击“加载”。

这样，Excel文件中的数据就被导入到Power BI中，可以根据需要进行进一步的处理。

#### 从数据库导入数据

从数据库中导入数据通常是进行复杂数据分析的第一步。Power Query支持连接到如SQL Server、Oracle、MySQL等多种数据库。以下是连接到SQL Server数据库的基本步骤：

1. 在“获取数据”窗口中选择“数据库”类别，然后选择“SQL Server”数据库。
2. 输入数据库服务器地址、身份验证信息，然后点击“确定”。
3. 选择需要查询的数据库表或视图，并点击“加载”或“编辑”。

导入数据库后，数据将出现在Power BI中，并可以使用Power Query进行转换和清洗。

### 2.1.2 数据转换的基本操作

导入数据后，通常需要执行一系列转换操作来准备数据进行分析。Power Query提供了一系列转换功能，包括添加列、合并查询、聚合数据等。

#### 使用“添加自定义列”

Power Query允许用户添加自定义列，来执行特定的数据处理任务。假设我们需要从一个包含日期的列中提取年份，可以使用以下步骤：

1. 在“添加列”选项卡中选择“自定义列”。
2. 在弹出的“添加自定义列”窗口中输入自定义列的名称和公式。
3. 例如，输入公式 `=Date.Year([Date])` 来提取年份。
4. 点击“确定”添加自定义列。

#### 使用“合并查询”

在某些情况下，需要将来自不同数据源的相关数据合并到一起。Power Query的“合并查询”功能可以实现这一点。以下是基本的合并查询步骤：

1. 选择“主页”选项卡中的“合并查询”功能。
2. 在弹出的“合并”窗口中选择需要合并的两个数据集。
3. 选择合并方式，如“内连接”、“外连接”等。
4. 选择需要匹配的列以创建关联。
5. 点击“确定”并根据需要对合并后的结果进行进一步处理。

在Power Query中执行数据转换时，每次操作都会在后台生成M语言代码，这些代码可以进行编辑、备份和共享，极大地方便了数据处理的复用和自动化。

## 2.2 PowerQuery的高级数据处理

在执行了基本的数据转换之后，可能需要进行更复杂的操作以满足高级数据处理的需求。Power Query提供了强大的数据合并和追加技术，以及高级编辑器来实现复杂的操作。

### 2.2.1 高级数据合并和追加技术

在处理多个数据源时，有时需要将多个数据表按行或列进行合并。例如，在数据分析中常常需要把不同时间范围的数据合并为一个完整的数据集。

#### 使用“追加查询”合并数据

“追加查询”功能允许用户将多个数据集按行顺序合并。假设我们有两个相同结构的销售数据表，分别代表两个不同的季度，可以按照以下步骤进行追加合并：

1. 选择“主页”选项卡中的“追加查询”功能。
2. 在弹出的“追加查询”窗口中选择需要追加的表。
3. 选择“追加”后，将创建一个新的查询，其中包含了追加后的数据。
4. 根据需要对追加后的数据进行进一步的清洗和转换。

#### 使用“合并查询”连接数据

与追加查询不同，合并查询功能通过共同的字段将两个或多个数据表中的相关数据行合并到一起。例如，合并员工信息表和销售数据表：

1. 选择“主页”选项卡中的“合并查询”功能。
2. 选择需要合并的两个查询，并指定作为连接条件的字段。
3. 选择合适的连接类型，如“内部”、“左外部”等。
4. 完成合并后，如果需要，可以删除不再需要的列。

### 2.2.2 使用高级编辑器进行复杂操作

高级编辑器提供了一个代码视图，允许用户手动编辑M语言代码，执行复杂的数据转换。在高级编辑器中，用户可以直接编写或修改M语言脚本，实现更复杂的操作。

#### 通过高级编辑器自定义转换

假设我们需要创建一个包含数据表所有字段值的摘要列，可以使用高级编辑器：

1. 在Power Query编辑器中，选择“高级编辑器”按钮。
2. 在打开的代码视图中，编写或修改M语言代码。
3. 使用M语言中的`Table.AddColumn`函数添加自定义列。
4. 在函数中编写代码生成摘要信息。
5. 编译代码无误后，点击“确定”应用更改。

通过高级编辑器，用户可以运用M语言的强大功能，实现自定义的复杂数据转换和清洗逻辑。

## 2.3 PowerQuery的性能优化

在数据处理过程中，性能优化是提高工作效率的重要环节。Power Query提供了一些策略和技术，以确保查询的效率。

### 2.3.1 查询性能优化策略

优化查询性能可以从多个角度进行，例如减少数据加载量、避免复杂的计算以及避免在查询中重复执行相同的操作。

#### 使用缓存避免重复计算

Power Query具有缓存机制，可以避免对相同数据的重复查询和计算。优化时应考虑以下几点：

- 减少不必要的高级转换操作。
- 避免在Power Query中进行复杂的数学计算，可以考虑将这些计算迁移到数据模型中。
- 对于需要重复使用的数据，可以考虑将其加载到数据模型中，以提高后续查询的速度。

#### 管理查询折叠以提高效率

查询折叠是指将Power Query中的多步转换折叠成单一的操作，直接在数据源上执行。这可以极大地减少数据处理所需的时间。

- 在进行数据转换时，应尽可能地使用那些能够实现查询折叠的操作。
- 例如，对大范围数据的筛选操作通常可以实现查询折叠，而某些复杂的聚合操作则可能不会。
- 在高级编辑器中，可以查看生成的M代码，并确保关键转换操作能够在数据源端执行，从而提升查询性能。

### 2.3.2 缓存管理和查询折叠

缓存管理和查询折叠是两个相辅相成的概念，正确的缓存管理策略配合高效的查询折叠技术，可以大幅提高数据处理速度。

#### 缓存的启用和管理

在某些情况下，可能需要手动管理缓存：

- 在Power Query编辑器中，可以对单个查询启用或禁用缓存。
- 如果确定某些数据不会经常变化，可以考虑在数据获取阶段将这些数据加载并存储到缓存中。
- 通过手动控制缓存，可以避免重复的远程数据加载，从而提高整体的处理效率。

#### 利用查询折叠减少数据传输

通过在数据源端执行尽可能多的操作，可以减少加载到Power BI中的数据量，从而提高性能。查询折叠技术帮助实现这一点：

- 一些Power Query转换操作，如筛选、排序、聚合