【数据分析平台构建】：Power Query与Excel数据模型，打造分析神器


参考资源链接：[Power Query教程：从入门到深度开发](https://wenku.csdn.net/doc/6412b75bbe7fbd1778d4a016?spm=1055.2635.3001.10343)
# 1. 数据分析平台构建概述

在现代企业中，数据分析平台扮演着至关重要的角色，它不仅提供了数据处理、分析和可视化的能力，而且能够帮助企业做出基于数据的决策。本章将概述数据分析平台的构建过程，旨在为读者提供一个全面的认识。

数据分析平台的构建是一个涉及多个阶段的复杂过程，其中包括数据的收集、处理、分析、报告以及决策制定。在构建这样一个平台时，通常要遵循以下几个基本原则：

1. **数据质量的保证**：只有准确、一致且及时的数据，才能确保分析结果的可靠性。因此，数据清洗和数据质量控制是构建平台时必须考虑的关键要素。
2. **易用性与扩展性**：构建平台应兼顾用户体验和系统的可扩展性，以便随着业务需求的增长，平台仍能保持高效的运行。
3. **安全与合规**：数据分析平台必须遵守相关的数据保护法规，并确保数据在处理和传输过程中的安全性。

下面章节将会更深入地探讨Power Query在数据处理与分析中的应用，以及如何有效地构建和优化Excel数据模型，为数据分析平台的搭建打下坚实基础。

# 2. Power Query的基础应用

## 2.1 Power Query的核心功能和界面概览

### 2.1.1 获取和转换数据的基本步骤

Power Query提供了强大的数据获取和转换功能，它允许用户从多种源（如Excel表格、数据库、Web页面等）导入数据，并通过一系列转换步骤将其清洗和准备成结构化数据。其基本操作步骤如下：

1. **打开Power Query编辑器**：从Excel中的“数据”选项卡开始，选择“从表/区域获取数据”，或者使用“获取数据”按钮来启动Power Query。

2. **选择数据源**：在弹出的“获取数据”界面中，选择所需的数据源类型。

3. **浏览并选择所需数据**：在选择特定的数据源后，Power Query允许用户浏览该源并选择具体的数据集。

4. **转换数据**：使用Power Query编辑器中的各种工具（如删除列、更改数据类型、添加自定义列等）来转换数据，使其满足需求。

5. **加载数据到Excel工作表**：转换完成后，用户可以选择将清洗后的数据加载回Excel工作表，或加载到数据模型中供进一步分析使用。

### 2.1.2 Power Query编辑器的主要组件

Power Query编辑器是一个图形化的界面，它具备以下核心组件：

- **查询设置窗格**：在此窗格中，可以查看和编辑查询的设置和步骤。
- **数据视图**：显示当前数据状态的表格视图，支持直接的数据操作和编辑。
- **公式栏**：显示和编辑查询中使用的M语言代码。
- **添加列菜单**：提供各种数据转换功能，如添加自定义列、合并列等。
- **转换菜单**：用于对现有列执行操作，例如更改数据类型、分组等。
- **查看菜单**：控制编辑器中的不同视图和功能，如应用步骤、刷新预览等。

## 2.2 数据清洗与准备

### 2.2.1 数据预览和删除重复项

在数据清洗的开始阶段，了解数据的基本情况是非常重要的。Power Query的预览功能可以查看数据集的头部和尾部，帮助识别数据格式和结构问题。

接下来是删除重复项。在数据预览窗口中，选择“删除重复项”，Power Query会自动处理并移除重复的数据行。如果需要保持某些列的数据唯一性，可以将这些列作为删除重复项的依据。

### 2.2.2 分列、合并和拆分列操作

数据清洗过程中往往需要对某些文本列进行进一步拆分或合并以提取所需信息。例如：

- **分列**：当一个列中的数据包含分隔符时（例如逗号分隔的电子邮件地址列表），可以使用“分列”功能来按分隔符拆分为多个列。

- **合并列**：可以将多个列合并成一个单一列。例如，名字和姓氏可以合并为全名。

- **拆分列**：与合并列相反，当一个列包含过多的信息时，可以将其拆分成多个列。例如，一个包含全名的列可以拆分为名字和姓氏两列。

### 2.2.3 使用高级数据处理功能

Power Query的高级数据处理功能包括但不限于：

- **数据类型转换**：确保每个列的数据类型（如文本、数字、日期等）正确无误。

- **填充空值**：使用“填充”功能可以向上或向下填充空值，也可以基于相邻的值或特定值填充。

- **添加自定义列**：用户可以添加新的列，基于现有数据和M语言表达式进行复杂计算。

- **使用函数**：Power Query支持使用诸如Text、Number、Date等内置函数，进行数据的进一步处理和转换。

下面的代码块展示了如何在Power Query中使用M语言来清理数据，并填充空值：

let
    // 从Excel表中加载数据
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    // 删除重复项
    RemoveDuplicates = Table.Distinct(Source, {"Name", "Email"}),
    // 填充空值
    FillNullValues = Table.FillNulls(RemoveDuplicates),
    // 更改数据类型
    ChangeTypes = Table.TransformColumnTypes(FillNullValues,{{"Name", type text}, {"Email", type text}})
in
    ChangeTypes

在此代码块中，首先加载名为"Table1"的工作表数据，然后删除了包含相同"Name"和"Email"的重复项。接着，使用`Table.FillNulls`函数填充了所有空值。最后，通过`Table.TransformColumnTypes`函数将"Name"和"Email"列转换为文本类型。

## 2.3 数据合并和连接

### 2.3.1 从不同数据源导入数据

Power Query允许用户从各种数据源导入数据，这些数据源包括但不限于Excel文件、CSV文件、数据库、Azure、Web等。例如，若要合并来自不同Excel文件的数据，可执行以下步骤：

1. 选择“从文件”→“从Excel”以打开文件对话框。
2. 选择要导入的文件或文件夹。
3. 选择导入方式：“合并”或“追加”。

选择“合并”将允许用户选择两个或多个数据集进行合并，而“追加”则将数据集添加到现有数据的底部。

### 2.3.2 表之间的合并与连接技巧

在合并数据时，重要的是选择正确的连接类型。Power Query提供了多种连接方式：

- **内连接**：仅保留两个表中匹配的数据行。
- **左外部连接**：保留左表中的所有行，即使右表中没有匹配的行。
- **右外部连接**：保留右表中的所有行，即使左表中没有匹配的行。
- **完全外部连接**：保留两个表中的所有行，无论是否匹配。

合并连接的关键是在连接属性中正确选择连接字段。例如，若需要将“产品”表和“销售”表基于产品ID合并，可以使用如下步骤：

let
    // 加载产品表
    Products = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    // 加载销售表
    Sales = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    // 基于产品ID合并两个表
    Merged = Table.NestedJoin(Products, {"ProductID"}, Sales, {"ProductID"}, "SalesData", JoinKind.LeftOuter)
in
    Merged

在此代码块中，首先加载了两个表“Table2”（产品表）和“Table3”（销售表）。然后使用`Table.NestedJoin`函数执行了基于"ProductID"的左外部合并。

### 2.3.3 合并查询的高级选项

在数据合并的高级选项中，Power Query提供了更精细的控制，例如：

- **自定义列名**：合并后可以自定义新列的名称。
- **指定列数据类型**：合并后的列可以指定数据类型。
- **启用高级选项**：高级选项允许用户执行更多高级操作，如在合并过程中添加索引列或在所有匹配行中复制列。

在处理复杂数据时，这些高级选项可大大提升数据处理的灵活性和准确性。使用Power Query的合并功能，可以实现数据的丰富分析和高级报告。

## 2.4 数据聚合与分组

### 2.4.1 使用分组与聚合函数

Power Query的分组与聚合功能允许用户按指定列的值对数据进行分组，并对每个组执行聚合计算。常用的聚合函数包括：

- **求和** (`Sum`)
- **平均值** (`Average`)
- **最小值** (`Min`)
- **最大值** (`Max`)
- **计数** (`Count`)

例如，若要按“产品类别”分组，并计算每个类别的总销售额，可以执行如下操作：

let
    // 加载数据表
    SalesData = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    // 按产品类别分组并计算总销售额
    Grouped = Table.Group(SalesData, {"Category"}, {{"TotalSales", each List.Sum([SalesAmount]), type number}})
in
    Grouped

在这个例子中，首先加载了名为"Table4"的数据表。然后使用`Table.Group`函数按"Category"列分组，并在新列"TotalSales"中为每个类别计算销售额之和。

### 2.4.2 高级聚合技术

高级聚合技术可以实现复杂的数据分析。例如，计算每个类别的平均销售额，并找出平均销售额最高的类别：

let
    // 加载数据表
    SalesData = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    // 按产品类别分组，计算平均销售额，并将结果展开为单独的行
    AdvancedGroup = Table.Group(SalesData, {"Category"}, {
        {"AverageSales", each List.Average([SalesAmount]), type number},
        {"AllSales", each _[SalesAmount], type number}
    }),
    // 展开AllSales列，使每个销售额单独成行
    ExpandSales = Table.ExpandListColumn(AdvancedGroup, "AllSales"),
    // 排序平均销售额，找出最高值
    Sorted = Table.Sort(ExpandSales, {{"Ave