【Excel与Power Query整合：数据清洗与转换的艺术】


# 摘要
本文旨在全面介绍Excel与Power Query的强大功能及其在数据处理中的应用。首先，对Excel与Power Query的基本概念和数据获取技巧进行介绍，重点阐述了如何高效地从多种数据源导入数据，并进行高级连接和性能优化。随后，深入探讨了数据清洗的艺术，包括基础的整理技巧、利用Power Query进行筛选以及数据聚合与分组操作。文章还讲解了数据转换的高级应用，包括M语言的使用和数据合并与重构的策略。最后，展示了Power Query在报告与分析中的实际应用，如如何通过数据洞察创建动态图表和交互式数据报告，并提供了维护和分享Power Query解决方案的策略。

# 关键字
Excel；Power Query；数据清洗；数据转换；报告分析；性能优化

参考资源链接：[Excel中英文术语对照全览](https://wenku.csdn.net/doc/4v5802f3dh?spm=1055.2635.3001.10343)
# 1. Excel与Power Query简介

## 1.1 Excel的传统作用与限制
在企业数据管理与分析领域，Microsoft Excel一直是不可或缺的工具。它以表格形式存储数据，提供了强大的计算和图表展示功能，广泛应用于各种业务报告和财务分析中。然而，随着数据量的增加和处理需求的日益复杂，传统Excel在处理大数据集、多源数据合并及自动化处理方面逐渐显现出其局限性。

## 1.2 Power Query的出现
为了解决Excel中的数据处理难题，微软引入了Power Query。它是一个强大的数据连接和数据转换引擎，可与Excel紧密集成，提供更高级的数据获取、清洗、转换和加载功能。Power Query的引入，不仅大幅度扩展了Excel的数据处理能力，而且使其操作更加直观、高效。

## 1.3 本章内容概览
本章将为读者详细介绍Excel与Power Query的基础知识，包括Power Query的界面布局、基本功能和应用场景。我们将通过案例展示如何通过Power Query简化工作流程，提升工作效率。同时，也会讨论在Excel环境下，如何利用Power Query来实现数据的高效整合和前期处理，为进一步的数据分析打下坚实的基础。

# 2. Power Query的数据获取技巧

## 2.1 导入数据的基础操作

### 2.1.1 连接到不同数据源

Power Query作为Excel中的强大数据获取工具，提供了与多种数据源的连接能力。它支持从各种静态文件（如CSV、JSON、XML、文本文件）到动态数据源（如数据库、在线服务、Web API等）的连接。连接数据源是任何数据分析流程的第一步，也是最基础的步骤。

为了连接到不同数据源，用户首先需要打开Excel中的“数据”选项卡，并点击“获取数据”按钮。在这个菜单中，可以找到几乎所有的数据源类型，包括但不限于“来自文件”、“来自数据库”、“来自Azure”、“在线服务”等。每个类别下还有细分的数据源类型，用户只需选择适合的连接类型，输入必要的连接信息，例如数据库服务器地址、登录凭证等，即可成功连接到数据源。

下面是一个连接到本地CSV文件的示例代码块：

let
    Source = Csv.Document(File.Contents("C:\data\sales.csv"),[Delimiter=",", Columns=5, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    Data0 = Source{0}[Data],
    Data1 = Text.ToList(Data0),
    Data2 = List.Zip({Data1,Data1,Data1,Data1,Data1}),
    Data3 = List.Skip(Data2, 1),
    Data4 = List.Transform(Data3, each Text.Combine _),
    Data5 = Table.FromList(Data4, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Data6 = Table.TransformColumnNames(Data5, each "Column" & Text.From(_))
in
    Data6

此代码块展示了如何使用M语言从CSV文件中读取数据。首先，使用`Csv.Document`函数加载CSV文件，然后处理文件内容，将其转换为可操作的表数据。`Table.FromList`函数将数据列表转换为表格，并通过`Table.TransformColumnNames`为列指定名称。

### 2.1.2 数据导入与工作表融合

导入数据后，我们需要将这些数据导入到Excel工作表中。Power Query提供了一个叫做“关闭并加载到”的选项，可以让用户直接将数据加载到新的工作表中或现有的工作表的指定位置。这种方式不仅可以使数据被更方便地查看和编辑，还可以与其他Excel功能结合，比如使用数据模型进行数据分析。

为了更细致地控制数据如何被导入，用户可以在“关闭并加载到”对话框中选择不同的加载选项，如加载到“仅创建连接”、“加载到工作表”、“加载到数据模型”等。例如，如果你希望数据可以直接与现有的PivotTable（数据透视表）结合，你可能会选择“加载到数据模型”选项。

// 加载数据到新的工作表
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    NewSheet = Sheet1
in
    Excel.Workbook(Sheet1, true, true)

上面的代码块展示了如何使用M语言创建一个新的工作表，并把名为"Table1"的表数据加载到其中。这是在“关闭并加载到”对话框中，选择“仅创建连接”或“加载到新工作表”选项时，Power Query背后执行的操作的简化版本。

## 2.2 数据源的高级连接技术

### 2.2.1 跨文件合并与连接查询

在数据分析和报表过程中，经常需要将不同文件中的数据合并到一起。Power Query可以实现跨工作簿甚至跨文件夹的文件合并。使用“从文件夹”连接功能，Power Query能自动识别文件夹中的所有相关文件，并将它们合并为一个查询，极大提高了数据整合的效率。

跨文件合并一般涉及以下步骤：
1. 选择“从文件夹”选项，浏览到包含目标文件的文件夹。
2. 选择需要导入的文件类型，并加载。
3. 进行初步的数据整理，比如去除不必要的列。
4. 使用“合并查询”功能，按照特定的键值（例如ID或日期）进行数据合并。

下面是一个示例代码块，展示了如何实现跨文件的合并操作：

let
    // 载入文件夹中所有的CSV文件
    Source = Folder.Files("C:\data\sales"),
    // 保留文件名，丢弃其他文件属性列
    Files = Source{[Kind="File"]}[Data],
    // 为每个文件创建独立查询
    TableFromFiles = Table.TransformColumns(Files, {"Content", each _}),
    // 将每个文件的内容从CSV转换为表格
    AddedCustom = Table.AddColumn(TableFromFiles, "Custom.1", each Csv.Document([Content])),
    ExpandedCustom = Table.ExpandTableColumn(AddedCustom, "Custom.1", Table.ColumnNames(AddedCustom{0}[Custom.1])),
    // 合并所有表格
    MergedTables = Table.Combine({ExpandedCustom}),
    // 删除辅助列
    RemovedColumns = Table.RemoveColumns(MergedTables, {"Content"})
in
    RemovedColumns

### 2.2.2 多表合并与自动建模

在处理来自不同数据源的数据时，经常需要根据共同的标识符将它们合并，以进行更加深入的分析。Power Query提供了强大的合并查询功能，使得合并来自多个数据源的数据变得轻而易举。

多表合并的主要步骤通常如下：
1. 在Power Query编辑器中，选择需要合并的表。
2. 使用“合并查询”功能，选择一个或多个列作为合并的依据。
3. 选择合并操作，如内连接、左外连接等。
4. 应用合并并加载结果。

自动建模是指在多表合并之后，Power Query会智能分析数据间的关联关系，并允许用户通过简单操作创建复杂的数据模型。这一功能尤其适用于数据关系较为复杂的场景，可以帮助用户在不编写复杂代码的情况下，快速搭建数据模型。

在Excel的Power Query编辑器中，可以通过选择“数据模型”来启用自动建模功能。然后，在创建连接时选择“创建连接表”来自动建立表之间的关系。

// 合并两个表的示例代码
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Source2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    MergedTables = Table.NestedJoin(Source, {"JoinColumn"}, Source2, {"JoinColumn"}, "Table2", JoinKind.LeftOuter)
in
    MergedTables

此代码块使用M语言