Power Query精通指南1：查询结构设计、数据类型、数据导入与迁移（平面文件、Excel、Web）

全文参考《精通 Power Query》， 点此下载本文所有示例文件。

2. 查询结构设计	描述	操作方法	应用场景/优势
多查询体系	原始数据查询、暂存查询 和数据模型查询	原始数据查询：从数据源提取数据，初步清理 暂存查询：处理大部分转换工作 数据模型查询：执行最后步骤，加载到目标区域	提高数据处理效率和可维护性 便于扩展和适应复杂数据需求
暂存查询	暂存查询结果被缓存 后续引用直接使用缓存结果	第一次引用暂存查询时，结果被缓存 后续引用直接使用缓存	避免重复数据加载和处理 显著提高刷新速度
查询的引用	创建新查询并引用现有查询	右键单击查询，选择【引用】	建立查询之间的依赖关系 便于分层处理
拆分现有查询	将单个查询拆分为多个查询	右键转换步骤，选择【提取之前的步骤】	便于重构查询，优化查询结构
查询依赖可视化	直观了解查询之间的引用关系	使用Power Query的查询依赖项查看器 使用Monkey Tools插件	方便管理和优化查询结构，避免循环依赖
查询加载方式	加载到工作表、数据模型 或仅创建连接	Power BI：右键查询，取消勾选【启用加载】 Excel：在Power Query编辑器中或【查询 & 连接】窗格进行设置	Excel中可先全设为仅限连接 再将需要的查询加载到表
查询管理	对查询进行分组管理	在Power Query编辑器或【查询 & 连接】中 通过【新建组】来完成

3. 数据类型与错误	描述
数据类型与格式	数据类型影响数据的处理和存储，而数据格式仅影响显示方式，不改变实际值和精度。 Power Query编辑器只处理数据类型。点击【转换】 选项卡下的 【检测数据类型】，可自动检测并转换数据类型
数据类型转换顺序	将数值先转换为整数，再转换为小数，会导致小数部分丢失。 数据类型转换顺序很重要，警惕无意中的数据精度丢失
数据类型推断	Power Query默认会预览前1000行数据来推断数据类型。 如果特定值出现在第1001行之后，可能导致数据类型推断错误
数据类型的重要性	当数据类型未定义时，Power Query会尝试猜测数据类型，可能导致数据被错误解释和处理。 在加载数据前的最后一步，明确指定数据类型
查询加载后出错	Power Query编辑器默认只显示前1000行数据（预览模式），如果错误发生在1001行之后，可能出现预览OK但加载后出错的情况

4. 迁移查询	描述	特点
复制粘贴模式	复制查询时自动复制依赖的前序查询 不支持数据模型结构或度量值的导入	操作简单，适合小规模迁移
导入模式 Excel→Power BI	打开Power BI，选择【文件】【导入】 【Power Query, Power Pivot, Power View】	1. 导入Excel表：所有未加载到Power Pivot的查询默认为连接模式 2. 导入模型（复制数据）：导入所有查询和数据模型组件 数据完全包含在Power BI文件中，便于分享和分发 3. 导入模型（保持连接）：仅包含连接信息，无文件大小限制
导入模式 Power BI→Excel	使用Monkey Tools将Power BI模型导入Excel	不支持Power BI中的某些高级功能

5. 从平面文件导入数据	说明
平面文件特点	平面文件（TXT、CSV等）没有描述文件内容的额外信息（Schema），因此需要通过导入设置来进行文件解析。
设置系统默认值	Power Query会根据Windows控制面板中的区域设置来解析数据，包括日期、数字和货币格式。
手动指定区域设置	可在Power Query中手动指定正确的区域设置，避免不同用户因为默认区域设置不同而导致文件打开后数据不一致

6. 从Excel导入数据	说明	内部导入方式	外部导入方式
表（Table）	通过Ctrl + T创建的结构化对象 具有固定的标题行和格式化功能。	直接导入	设置好路径即可导入
区域（Range）	简单的矩形单元格范围，没有额外的结构化功能。	需要转换为表或命名区域后才能导入	无法直接从外部工作簿的 普通区域导入数据。
命名区域（Named Range）	对某个区域进行命名，方便通过名称引用。	可以通过名称框选择命名区域并导入数据。	可以导入
动态区域（Dynamic Range）	通过Excel公式计算得出的单元格范围 可以根据数据的变化自动扩展。	可以通过公式连接到动态区域并导入数据。	无法导入
工作表（Sheet）	Excel工作簿中的一个页面	可以通过设置打印区域间接导入整个工作表的数据。	可以导入
导入建议	基于 整个Excel 表构建解决方案 将数据源与业务逻辑分离

11. 基于 Web 的数据源	说明
Web数据源	包括Web文件和HTML网页内容，通过输入URL进行验证连接。
“自然表”和“建议表”	Power Query会尝试识别网页中的表格结构，生成“自然表”或“建议表”。
使用示例添加表	如果网页中没有明确的表格，可以通过输入示例数据引导Power Query自动填充整列。
手动提取数据	当网页中没有明确的表格结构时，需要手动定位和提取数据（逐层钻取HTML元素，直到找到包含所需数据的部分）。

零、Power Query简介

  数据分析师的工作通常涉及从数据源提取数据、转换数据、合并数据表、重塑数据结构等。Excel 公式、VBA 和 SQL 是传统数据处理的常用工具，但它们需要较高的技能水平和大量的时间来掌握和维护，这种依赖少数专家的方式存在风险，一旦这些专家离职，后续维护会变得困难。

0.1 Power Query 主要功能

  Power Query 的功能：ETL：Power Query 可以理解为一种 ETL（Extract Transform Load） 工具，其主要目的是在数据进入目标区域（如 Excel 表或 Power BI 数据模型）之前，对数据进行ETL。

• 提取（Extract）：可以从多种数据源（如文本文件、CSV 文件、数据库、网页等）导入数据。
• 转换（Transform）：支持数据清洗、整合和增强。
  • 数据清洗：包括过滤数据库中的数据、去除空值、导入并格式化不同地区的日期格式等
  • 数据整合：合并多个数据表，包括垂直追加（纵向合并）、水平合并（横向连接），以及执行分组等其它操作
  • 数据增强：扩展和丰富数据，比如添加新列、执行数学计算（如“销售数量 × 销售价格”生成“销售总额”）、动态创建表（如生成动态日历表）等等。
• 加载（Load）：可以将数据加载到 Excel 表、Power Pivot 数据模型、Power BI 数据模型等。
  

0.2 Power Query 的优势

• 易用性：Power Query 提供了直观的用户界面，即使是非技术用户也能快速上手，无需复杂的编程知识。
• 高效性：Power Query 可以快速导入、清洗和转换数据，大大减少了数据准备的时间。
• 可维护性：Power Query 的操作步骤可以被记录和重复使用，用户可以轻松刷新数据，而无需重新编写代码。
• 可移植性：Power Query 的解决方案可以在 Excel、Power BI、Power Automate 等多个工具中复用，提高了工作效率。

0.3 Power Query 组件

为了便于理解，可以把 Power Query 想象成一个“洋葱”，它有很多层，这些层实际上是组成 Power Query 的核心组件：

• M 引擎：底层查询执行引擎，也叫 Vertipaq 引擎（开发时的版本代号），运行 M 语言编写的查询；
• M 查询：用 M 语言编写的命令集合；
• Power Query 用户界面：图形化界面，帮助用户创建和管理查询，也被称为 Power Query 编辑器。

  还有一个概念是Tabular 模型。Tabular 是一种用于数据建模的结构，主要用于存储和组织数据，以便进行高效的分析和查询。Tabular 模型 是 Microsoft Power BI、SQL Server Analysis Services 和 Excel 中用于数据建模的核心存储结构。以上关系用一句话总结：用户通过 DAX 语言告诉 DAX 引擎（Vertipaq 引擎）如何操作 Tabular 模型中的数据。

在最低限度上，一个产品至少集成有 M 引擎和 M 查询这两层：

产品	M 引擎	M 查询	Power Query 用户界面
Excel	是	是	是
Power BI 桌面版	是	是	是
Power BI Dataflows	是	是	是
SQL Server Integration Services	是	是	否

一、Power Query数据处理基本流程

1.1 前期准备

在开始 Power Query 使用之前，建议对 Power Query 界面的默认设置做一些更改。

• 调整 Excel 默认设置：本文中使用的绝大多数场景、插图和案例都将使用 Microsoft 365 版本的 Excel 进行展示

  1. 单击【数据】选项卡【获取数据】【 查询选项】。

  2. 在【全局】【数据加载】确保勾选【快速加载数据】的复选框（这个设置将在刷新过程中会锁定 Excel 的用户界面，将确保在继续使用数据之前拥有的数据是最新的）。

  3. 在【全局】【Power Query 编辑器】下，确保这里的每一个复选框都被勾选。特别要确保【显示编辑栏】的复选框被勾选。

• 调整 Power BI 默认设置

  1. 单击【文件】选项卡【选项和设置】【选项】。

  2. 在【全局】【Power Query 编辑器】下，确保这里的每一个复选框都被勾选。特别要确保【显示编辑栏】复选框被勾选。

• 数据下载：点此下载本文所使用所有数据。

1.2 提取

ETL 过程从“提取”步骤开始。在这个步骤中有四个不同的子任务：

1. 选择数据：通过【获取数据】功能选择数据源，此次示例选择 “第 01 章 示例文件\Basic Import.csv”。下图左侧是excel处理方式，右侧是PoweBI Desktop处理方式。
   
2. 身份验证：许多数据源在连接到它们之前都需要进行身份验证，比如web数据。对于本地文件，通常不需要额外的身份验证（本机用户有权限能访问它）。
3. 预览窗口：一旦选择了文件，就会打开预览窗口，用于确认数据结构是否正确。一般很少需要在这里更改任何内容，因为 Power Query 在大多数情况下都能做出正确的默认选择，你也可以调整文件编码、分隔符和数据类型检测等设置。
   
4. 查询处理：确认数据是否需要进一步转换。如果数据已经干净，可以直接【加载】；如果需要调整，点击【转换数据】进入 Power Query 编辑器。

1.3 转换

1.3.1 Power Query 编辑器界面

1. 功能区： 位于屏幕的顶部，有四个选项卡：【主页】、【转换】、【添加列】和【视图】。

2. 查询导航窗格： 显示其中所有的 Power Query 查询列表；

3. 编辑栏： 若没有显示，在【视图】选项卡中勾选【编辑栏】复选框即可。

4. 当前视图窗口： 数据预览区域

5. 状态栏： 位于屏幕的底部，它提供了列数、行数的汇总信息，以及用于显示列分析统计的行数指标，还有一个位于最右边的指标，显示预览数据的最后更新时间。

6. 属性窗口： 这里显示当前预览内容的查询名称，与左边查询窗口中的查询名称一致。

7. 应用的步骤窗口： 显示所有数据处理步骤，并且在重新导入数据时会将已有的转换应用于整个数据集。

1.3.2 默认转换

在首次导入文件时，Power Query 会自动执行以下步骤：

• Source：默认情况下，每个查询的第一步都被称为“Source（源）”
  

• Promoted Headers：将第一行作为列标题。
  Power Query 的内部算法解析了数据源的内容并以表显示。第 1 行看起来与接下来的几行不同，它看起来像一个标题。
  

• Changed Type：自动检测并设置数据类型。
  这个步骤背后的逻辑是，Power Query 已经扫描了每一列的前 200 个值，并对这些列的数据类型做出了判断。然后它自动增加了这一步，在将数据加载到目的地之前“锁定”这些数据类型。
  

1.3.3 自定义转换

  根据业务需求，可对数据进行进一步的处理，比如删除不需要的列、重命名列等，每次操作都会在【应用的步骤】窗口中生成一个新步骤。Power Query 提供了一个安全的环境，让用户可以放心尝试各种操作，而不用担心损坏原始数据。

• Power Query 不会修改原始数据：它只在数据的副本上操作，所以无论用户怎么折腾，原始数据始终保持不变。
• 可以随意尝试和撤销操作：用户可以大胆点击任何按钮或尝试任何功能，看看会发生什么。如果某个操作的结果不满意，用户可以轻松删除这个操作步骤，恢复到之前的状态。

1. 删除“POS Hour”列：可右击并选择【删除】，也可直接按下DEL 键进行删除；
2. 重命名列： 通过右键单击或左键双击列名，将“Item Name”列【重命名】“Item”；Units Sold”列重命名为“Units”。
   

至此，我们已经：

1. 连接到“CSV”数据源。
2. 将第一行提升为标题并设置了数据类型。
3. 删除了一个不相关的列。
4. 重新命名了两列，使它们更加易于理解。

1.4 加载

1.4.1 自动检测数据类型

  在确定最终查询之前，为数据集中的每一列重新定义数据类型是一个良好的习惯，事实上，微软也是这样做的。逐个修改太过麻烦，更高效的方式是自动检测并修改数据类型。

1. 单击任意一列，然后按 Ctrl + A 选择所有列。
2. 转到 【转换】 选项卡，点击 【检测数据类型】。
   
   这个步骤被命名为“Changed Type1”，因为之前已经有一个“Changed Type”步骤。你也可以可以重命名步骤以提高可读性，比如将名称改为“Lock in Data Types（调整数据类型）"。

唯一不能以这种方式重命名的步骤是“Source”步骤。要重命名“Source”步骤，需要编辑查询的 M 代码。

1.4.2 重命名查询

  默认情况下，一个查询步骤会使用数据源的名称作为查询的名称，比本次查询默认为“Basic Import”。转到 【查询设置】窗格【属性】【名称】，将名称改为“Transactions”

1.4.3 加载查询

• Excel：转到 Power Query 【主页】选项卡，点击【关闭并上载】，数据将加载到新工作表的表中。
  

  1. 查询窗格： 这将始终与 Power Query 编辑器中定义的查询名称相匹配。右击查询选择编辑，或者直接双击查询，可以打开 Power Query 编辑器。

  2. 表的名称： 这通常与查询的名称相匹配，但非法字符将被替换为“_”字符，与其他工作表名称的冲突将通过在查询名称的末尾添加一个数字值来解决。

  3. 工作表名称： 这通常与查询的名称相匹配，但非法字符将被替换为“_”字符，名称太长的情况可能会被截断，与其他现有表格名称的冲突的情况，将通过在查询名称的末尾添加括号内的数字值来解决。

• Power BI：转到 Power Query【主页】标签，点击【关闭并应用】，数据将加载到数据模型中。
  

Power BI 会默认按第一列对数据进行排序。要在 Excel 中做到这一点，需要在数据加载之前添加一个明确的步骤来对数据进行排序。

1.5 刷新查询

当源数据更新时，可以通过刷新功能重新加载数据，不必再执行数据清洗工作。

• Excel：点击【数据】【全部刷新】，一旦数据加载完毕，Excel 将在【查询 & 连接】窗口中显示加载的总行数；
• Power BI：点击【主页】【刷新】，Power BI 中的刷新进度可以在【数据】区域查看。

Excel（左）和 Power BI 桌面版（右）中的查询加载进度显示

1.6 编辑查询

1.6.1 启动 Power Query 编辑器

• Power BI：转到【主页】标签并单击【转换数据】
  
• Excel：进入【数据】选项卡【获取数据】【启动 Power Query 编辑器】；或是进入【查询 & 连接】窗口，右击/双击任意查询【编辑】。

1.6.2 检查步骤

  在 Power Query 编辑器中，可以通过 【应用的步骤】 窗口查看每一步操作的结果。由于数据预览使用了缓存技术，如果怀疑数据预览过时，可以点击 【刷新预览】 按钮，确保显示的是最新数据。

1.6.3 修改步骤

  如果直接打开提供的示例文件Basic Import - Complete.xlsx，可能会发现无法刷新查询，因为“Source”步骤指向的文件路径是原作者系统中的路径，不是用户本地文件路径，需要进行修改。

  在【应用的步骤】窗口中，某些步骤名称旁边有一个小齿轮图标，表示可以重新配置这些步骤；反之，如果某个操作在执行时打开了一个配置界面，那么在该步骤旁边会出现齿轮图标。“Source”步骤就有一个齿轮图标，单击它可以打开一个配置界面：

  第一个字段是【文件路径】，用于更改数据源的路径，选择 “第 01 章 示例文件\New Data.csv”。更改之后，保存并加载数据，可以看到数据已经从 4,575 行加载到 4,921 行。

一般来说，Power Query 在导入数据时会正确配置其它选项，确有错误，可以进行更改。

二、查询结构设计

2.1 多查询体系

2.1.1 分层查询

  Power Query 作为一款强大的 ETL工具，其查询结构设计对于数据处理的效率、可维护性和可扩展性具有至关重要的影响。随着数据项目规模的扩大和复杂性的增加，单一查询往往难以满足需求。采用多查询体系结构可以显著提升数据处理的效率和可维护性。对 ETL使用如下分层架构：

1. 原始数据查询（Raw Data Query）：从数据源中提取数据，只进行初步的清理。通常只删除不使用的列或行，保留所有记录，形成一个干净的原始数据集，便于查看可用数据和后续处理；
2. 暂存查询（Staging Query）：处理ETL过程中的大部分转换工作，包括数据筛选、清洗、转换等，创建干净的表供后续使用。根据需要也可以将它分解成多个查询。
3. 数据模型查询（Data Model Query）: 在加载前执行任何最后步骤，比如设置最终表的名称，追加或合并暂存层中的查询，为每一列设置最终的数据类型。

2.1.2 暂存查询提高查询性能

  在 Power BI 和 Excel 2019 及更高版本中，Power Query 使用了节点缓存技术——当暂存查询第一次被引用时，其结果会被缓存，后续引用该暂存查询的查询会直接使用缓存的结果，而不会重新执行暂存查询，这种机制可以显著减少重复的数据加载和处理，从而提高刷新速度。

  假设有一个从 CSV 文件中提取数据的查询链，如果使用暂存查询，在刷新数据时，暂存查询只执行一次，并且其结果被缓存，销售表查询和客户表查询都会引用这个缓存的结果。

  如果没有使用暂存查询，而是为每个表（如销售表和客户表）分别设置独立的查询链，每个查询链都需要从 CSV 文件中重新加载数据，并且独立执行暂存查询中的转换步骤。这会导致重复的数据加载和处理，显著降低性能。

  要注意的是，在设计查询时，原始数据查询和暂存查询不应设置为可加载的（即不应直接加载到数据模型或工作表中）。它们的作用是为后续的查询提供数据，而不是作为最终的数据输出，否则会导致以下问题：

• 不必要的数据加载：这些中间查询的结果会被加载到数据模型或工作表中，占用额外的存储空间。
• 性能下降：每次刷新时，Power Query 都会重新执行这些查询，会导致刷新时间比单查询更长。

正确做法是将它们设置为“仅限连接”（仅作为数据源被引用，不加载到数据模型或工作表中）。

2.1.3 单查询与多查询的对比

优势描述	分拆查询	单个查询
数据源管理	构建了单独的原始数据查询，所以方便切换数据源 创建一个新的“原始数据”连接，与旧的数据源并行存在，只要确保列名相同，就可以替代	需要将所有数据源声明在一个查询中
查询追踪	查询多，难以追踪沿袭（互相引用依赖的关系）	单一视图展示所有步骤，便于理解和修改。
查询查找	查询区域无搜索功能，查询过多会难以找到所需要的特定查询。	易于找到所需查询
查询效率	暂存查询可以被多个后续查询所引用，避免了对相同数据源的重复处理	-
移植性	-	SSIS 和 Azure Data Factory只支持单个查询
维度建模	良好的分层架构，便于扩展和适应复杂数据需求，适合 Power Pivot 和 Power BI 的维度建模。	-

公式防火墙错误：有时拆分查询会引发“Formula Firewall（公式防火墙）”错误，必须在一个查询中声明所有数据源才能克服这个问题。

2.2 查询的引用

2.2.1 创建引用

  在 Power Query 中，可以通过右击现有查询并选择 “引用” 来创建新的查询，从而建立查询之间的依赖关系。

  还是以上一章数据为例，在导入“Basic Import.csv”文件，执行默认的三个转换步骤后，将该查询重命名为Raw Data。右击Raw Data查询，单击【引用】，这将创建一个名为“Raw Data (2)”的新查询，将其重命名为Staging。使用同样的方式，引用Staging查询，重命名为Sales。

  我们对Staging查询执行同第一章中样的步骤：删除POS Hour列、将Item Name列重命名为Item、Units Sold列重命名为Units，可以看到，Staging查询中发生的任何事情都将流向Sales查询。

  最后在Sales查询中，选择所有列，点击【转换】选项卡中的【检测数据类型】选项，在加载前进行数据类型转换，至此就完成将 ETL 过程重新构建为三个独立的查询：

2.2.2 查询依赖可视化

  Power Query 提供了查询依赖项查看器，帮助用户直观地了解查询之间的引用关系。转到【视图】选项卡，选择【查询依赖项】，可以看到：

  另外还可以使用Monkey插件，它为创建和检查查询提供了更多多功能。一个关键的功能是“Query Sleuth（查询探测器）”，它提供了一个强大的查询追踪器，显示了“Calendar”查询背后的依赖树和 M 代码：

2.3 查询加载方式

2.3.1 仅创建连接

  对于暂存查询或其他中间查询，可以选择 “仅创建连接”，避免不必要的数据加载，提高操作效率。

• PowerBI：右击【查询】导航器中的“Staging”，取消勾选【启用加载】选项即可。可以看到仅限连接的查询，名称以斜体显示。
  

• Excel：

  1. 在Power Query编辑器中进行配置：PowerBI的Power Query编辑器可以单独配置每个查询的加载方式，Excel中只能统一配置一种加载方式。可以将所有查询配置为仅限连接，然后再使用方式2单独修改。
     

  2. 在查询-连接中进行配置：右键单击任何查询，选项“加载到”选项，进行逐一配置：
     

• 【表】： 将三个查询以表格形式加载到当前工作表或新工作表中。

• 【数据透视表】： 如果有一个单独的查询，这个选项将把数据加载到“数据透视表”中，并在现有/新的工作表中创建一个新的“数据透视表”，在这个案例中，有三个查询，它会将三个表加载到数据模型中，然后在一个新的工作表上创建一个新的“数据透视表”。

• 【数据透视图】： 遵循与“数据透视表”报告相同的方法，但创建一个“数据透视图”而不是“数据透视表”。

• 【仅创建连接】： 禁用每个查询的加载，直到更改这个设置（或通过另一个查询的引用，调用这个查询）。

  先在Power Query编辑器全配置为仅限连接，再只更新那些确实需要加载的查询是最效率的方式，因为【仅创建连接】的查询几乎是即时创建的。如果选择将三个查询加载到工作表或数据模型中， Excel 还需要为它们建立新的工作表或数据模型表，等待所有的数据加载完成后，再将两个表改为仅限连接，然后等待 Excel 再次更新，因为它删除了不必要的数据。

2.3.2 加载到工作表或数据模型

  对于最终需要展示或分析的数据查询，需要加载到工作表或数据模型中。进入 Excel 的【查询 & 连接】窗格，右击想更改的查询（ “Sales” ），选择【加载到】选项，选择“表”。

  如果一个表本来是想仅限连接，但是误加载到表，常规方式是更改加载目的地为“仅限连接”，然后删除加载的表。更高效的方式是直接删除误加载的工作表，Power Query 会自动将该查询的加载方式改为“仅限连接”。

2.4 查询管理

  如果查询数量变多，可以创建查询文件夹，将查询按照功能或数据来源进行分组，提高查询的可管理性和可检索性。创建时，为文件夹和查询提供清晰的名称和描述，可确保每个人都能够快速理解查询的目的和用途。

  新建组：在Power Query编辑器【查询】导航器窗格中或【查询 & 连接】窗格中，都可以新建文件夹。可以直接在空白处右击选项“新建组”，或者选择一个或多个查询，右击选择“移至组”-“新建组”。

  创建三个新组“Raw Data Sources”、“Staging Queries”、“Data Model”，移动查询。在Power Query编辑器中可自由拖拽查询，在【查询 & 连接】窗格中只能右击查询，【移至组】选择要放置查询的文件夹。

  把最重要的查询放在顶部，而把审查最少的放在底部。换句话说，希望看到的顺序是“数据模型”、“暂存查询”，最后是“原始数据”，使用 Power Query 编辑器中的拖放功能更自然。

右击现有文件夹【新建组】，可以创建子文件夹来将查询进行分组。

2.5 拆分现有查询

  对于已经存在的单查询解决方案，可以使用 “提取之前的步骤” 功能将其拆分为多个查询，以实现更优的查询结构设计。回顾在第 1 章中构建的查询，其中“Transactions”查询的有6步。

1. 选择“Transactions”查询，右击“Lock in Data Types”步骤【提取之前的步骤】， 输入“Staging”作为【新查询名称】。

2. 选择“Staging”查询，右击“Removed Columns”步骤【提取之前的步骤】，输入“Raw Data”作为【新查询名称】。

  在 Power Query 的查询结构设计中，多查询体系结构、查询的引用和依赖关系、加载目的地的选择，都需要综合考虑。通过合理的设计和优化，可以确保数据处理的高效性、可维护性和可扩展性。在实际应用中，可以根据项目的特点和复杂程度，灵活选择单个查询或分拆查询的方式

三、数据类型与错误

3.1 数据类型与格式

3.1.1 数据类型 VS 数据格式

  在 Excel 中，数据的显示格式（如数字、文本、日期等）往往与数据的实际类型混为一谈。然而，在 Power Query 中，数据类型和数据显示格式是两个完全不同的概念。以第 03 章 示例文件，Data Types vs Formats.xlsx为例，表格显示如下：

  “Whole”列中的第一个值是【整数】数据类型，其值为 9553 ，已被格式化显示为 9,553.000000 。表格中显示的每个数值都被四舍五入到第一列中显示的小数位数（数据类型），但它们都被格式化为显示 6 位小数（数据格式）。

• 数据格式：指数据的显示方式，它不影响数据的实际值或精度。例如，在 Excel 中，一个数值可以被格式化为显示两位小数，但这并不改变其实际值。
• 数据类型：数据的内在性质，它决定了数据可以进行的操作以及数据的存储方式。例如，数值型数据可以进行加减乘除运算，而文本型数据可以进行字符串拼接和查找操作。

  Power Query 支持五种主要的数据类型：数值型、日期和时间、文本、布尔型和二进制（文件），还有一些子类型，每种数据类型都有其特定的用途和转换规则。

  将以上表格导入Power Query，可以看到每一列的【数据类型】图标都显示为“ABC123”，表明该列的数据类型还没有定义，或者说该列中可能有混合的数据类型，此时Power Query 会显示其原始值。比如第三行，三四列数值有四位小数，但第2列只显示三位小数，因为这个单元格数值本来就只有三位小数。

  单击“Precision”列和“Whole”列上的【ABC123】图标，选择【整数】类型，可以看到9350.095 这个值已经被更改为 9350，不仅仅是在顶部列中，而且在窗口底部的数据预览中也同样改变了。将“Currency”列改为货币数据类型，将“Decimal”列改为小数类型，可以发现：

• 任意（Any）：默认情况下，未定义数据类型的列会显示为“ABC123”图标，此时Power Query 会显示其原始值。
• 整数（Integer）：将数值四舍五入为整数，丢失小数部分。
• 货币（Currency）：将数值四舍五入到小数点后四位，并显示两位小数。这种数据类型主要用于处理货币值，确保精度（外币汇率保留四位小数）。
• 小数（Decimal）：保留数值的全部精度，不进行四舍五入，尾数为零的小数不会显示。

3.1.2 设置数据类型的顺序

  简而言之，在 Power Query 中不需要设置格式。 Power Query 的重点是获取正确的数据，而不是呈现数据的外观。数据格式化应在数据展示层中进行，比如工作表中的单元格、度量值格式（如果数据被加载到数据模型中）、excel图表或PoweBI的视觉显示效果中。

  由于数据类型的更改会影响数值的精度，因此更改数据类型时，操作步骤的顺序至关重要。比如将“Whole”列继续更改其数据类型为小数，当出现“Changed Type”的提示时，选择【添加新步骤】，此操作首先会计算之前“Changed Type”的结果（转为整数），再将该列的数据类型更改为【小数】数据类型，这将会导致数据丢失小数部分（前一步转换为整数时就已经丢失了）。

  相反，当出现“Changed Type”的提示时选择【替换当前转换】而不是【添加新步骤】，它不会在原来的“Changed Type”中应用【整数】数据类型，而是直接将步骤更新为使用【小数】数据类型，小数的精度将被保留下来。

  在每次导入数据时，Power Query 都会自动应用“Changed Type”的步骤，所以每次都做检查是一个很好的习惯。默认情况下，Power Query 在设置数据类型时只预览前 1000 行。如果数据集中的第一个小数值显示在第 1001 行，Power Query会自动将该列数据设置为整数，后续再更改数据类型为小数，小数部分也已经被截取了。

  如果检查 Power Query 窗口底部的状态栏，会看到【基于前 1000 行的列分析】这句话，单击这行字，可以更改分析范围，比如【基于整个数据集的列分析】，而不是默认的 1000 行。

  为什么 Power Query 不直接覆盖上一步而是要弹出“Changed Type”对话框呢？因为有些数据类型在转换成另一种格式之前必须先转换成另一种中间格式。比如要将文本“2012-12-23 12 : 05 PM”转换为【日期】，必须先将其转换为【日期/时间】，再将【日期/时间】转换为【日期】。

3.1.3 数据类型的重要性

在 Power Query 中，必须要声明数据类型，错误地选择数据类型会影响数据的精确性。

• 函数需求：所有的 Power Query 函数都需要输入特定数据类型，否则会报错；而且Power Query 不会像 Excel 那样自动进行数据类型转换。
• 避免数据解释错误：当数据类型未定义时，Power Query 会尝试猜测数据类型，但这可能导致数据被错误地解释和处理。
  下图中，左侧查询未定义数据类型，“Undefined Dates”列看起来像日期。它们甚至是斜体的，这似乎表明它们确实是日期。将其直接加载到 Excel 工作表中，在没有定义数据类型的情况下它返回了一列数值（表示给定日期的日期序列号）；如果在数据加载时，勾选数据模型，将返回文本类型（数据左对齐）。如果检查数据模型，是可以确认这些日期确实是作为文本加载的。Power BI利用数据模型来存储数据，所以它将把未定义数据类型的日期也加载为文本。
  

  无论查看上面的哪个版本，这都不是想要的结果，更糟糕的是，仅仅更改加载目的地就会影响输出的结果，这正是未定义数据类型的真正危险所在——数据在不同环境中被不同地解释和处理，这可能导致数据分析结果不准确。

  另外，在合并不同数据集时，如果数据类型不一致，这些可能会被重置为未定义类型，需要额外的处理来确保数据类型的一致性。

  综上所述，不要依赖 Power Query 的猜测，在将数据加载到工作表或数据模型之前，而应明确指定数据类型，以确保数据的准确性和一致性。

3.2 常见错误类型

在 Power Query 中，错误主要分为两类：步骤级错误和值错误.

• 步骤级错误：会阻止特定步骤及后续所有步骤的执行，使查询无法加载。比如数据源不可用、列名不存在
• 值错误：值错误发生在单元格级别，通常由无效的数据类型转换（比如文本列设为数值类型）或不兼容的数据类型操作引起（比如将数值与文本进行数学运算）。

3.2.1 步骤级错误

  在编辑一个查询时，将默认选择该查询最后一步，如果查询中存在步骤级错误，Power Query 将在主预览区显示一条黄色的警告信息。比如在本地打开"第 03 章 示例文件"，ErrorTypes.xlsx文件，进入Power Query编辑器中， 会出现数据源错误：

1. DataSource.Error：以具体的错误类型开始。此示例表示有一个数据源错误， Power Query 无法找到数据源文件。
2. 详细信息：该区域指出导致错误的具体内容。在本例中，它是丢失文件的完整文件路径。
3. 【转到错误】按钮：若当前步骤不是错误的直接来源时，这个按钮就会出现。大多数情况下，单击此按钮将被直接带入导致错误的步骤。

  对于数据源错误，单击【转到错误】按钮将将转到“Promoted Headers”步骤。这种情况只要持续单击上一步，直到发现是哪个步骤导致的错误就行。

在 Power Query 编辑器中修改数据源错误，有三种方法：

1. 单击“Source”步骤旁边的齿轮图标
2. 单击错误信息中的【编辑设置】按钮，直接修改M公式
3. 进入【主页 】【数据源设置】选择【更改源】。
   

1和2只更新所选查询的数据源，3将更改数据源的所有实例，即使它被用于多个查询中。修改后还需要单击【刷新预览】，以使编辑器认识到数据源已经更新了。

也可以不在Power Query 编辑器中修改：

• Excel 中，可以在【数据】选项卡上的【获取数据】菜单的底部附近找到【数据源设置】
• Power BI 中，可以在【主页】选项卡的【转换数据】区域中找到【数据源设置】。

  另一个常见错误是找不到某列，常发生在“Changed Type”步骤，可能是列名错误或者该列被删除。可以修改正确的列名（硬编码），或者是通过公式动态计算，增加或删除列。

3.2.2 值错误

3.2.2.1 值统计

  虽然步骤级错误是 Power Query 中最严重的错误，但值错误可能更危险，因为它们常常并不明显。如果错误数据并不总是显示在数据预览中。那么如何识别列中存在的错误？

  如果使用的是 Power BI 或 Excel 365 ，将会注意到，在列的标题下有一条红色的短线，后面跟着条纹。这是一个视觉提示，表示该列中存在某种错误。

  此外，如果想看到关于列的更多细节，可以在【视图】选项卡中查看和更改这些设置——列质量、列分发、列配置文件。开启这些设置后，会发现在列的顶部有一些快速的统计数据和图表，用户可以据此评估数据质量。

• 【列质量】：提供有效、错误、空值的统计
• 【列分发】：使用图表显示数据集中非重复值和唯一值的数量。“Units Sold”列没有显示统计图表，因为列中有错误，处理之后会正确显示。
• 【列配置文件】：当选择一整列时，在屏幕底部提供了一个更详细的视图。

  这些项目往往要占用大量的空间，在工作中通常会取消勾选【列质量】和【列分发】的复选框 ，但勾选（保留）【列配置文件】。这样就可以在需要的时候通过选择单列来查看统计数据。

3.2.2.2 错误处理

  如果直接单击单元格中的“Error”一词，Power Query 将为查询添加一个新的步骤，并钻取到该错误。虽然可以看到错误详细信息，但是会失去预览窗口中的所有其他数据。相反单击单元格中“Error”关键词旁边的空白区域，Power Query 将在预览区下面显示错误信息的详细描述：

如果不小心单击了“Error”关键字，并创建了一个新步骤，只要删除它就可以返回到完整的数据预览

  检查发现前一个步骤中，这些单元格中的值是“N/A”，将其转为数字导致Power Query 抛出了一个错误，解决的方式有多种：

1. 错误值替换：在“Changed Type”步骤之前插入一个新的步骤，用“0”或“null”关键字替换“N/A”；或者是右击“Units Sold”列，选择【替换错误】，输入“0”或“null”（根据业务逻辑来选择）
2. 删除Units Sold列错误：选择“Units Sold”，然后转到【主页 】【删除行】【删除错误】
3. 删除所有列错误：选择所有的列，然后转到【主页】 【删除行】 【删除错误】。

  最谨慎的方法是替换错误，最直接的方法是删除任何列中有错误的行，删除之前，建议先浏览整个数据，以确保可以这样做。这里选项删除Units Sold列错误，就得到了一个漂亮干净的表格：

  另一种值错误类型是不兼容的数据类型，比如添加一列，输入公式：[Units Sold] * 10，结果整列都是错误：Expression.Error（由试图进行的数学运算触发错误）。

  在 Excel 中可能允许这样做，因为 Excel 在“Units Sold”列乘以 10 之前，会隐含地将“Units Sold”列转换为数值类型，而 Power Query 则会反馈：“不，不能这么做”。

3.2.2.3 多列处理

替换错误的 M 表达式为：

= Table.ReplaceErrorValues(Source, {{"Value", null}}) 			 // { "列名" , "替换为的值" } 

删除错误的 M 表达式为：

// 第一个参数是上一个步骤名称，第二个参数是要删除错误的列名
= Table.RemoveRowsWithErrors(#"Replaced Errors", {"KPI"})

在编辑器栏中可以对公式进行多列修改。

3.2.3 查询错误

  处理完数据源错误。列名错误和值错误，将查询加载到excel表时，在查询&连接窗口，显示数据总共有 4572 行，其中有 345 个错误。

  单击错误文本：“345 个错误”（实际上是一个超链接），将会启动 Power Query 编辑器，看到一个名为“ErrorData 中的错误”的新查询。这个查询只保留有错误的行，并为每个错误行添加了一个行号，即错误是从导入的文件的第 3,882 行开始的，所以之前看不到。

  Power Query 限制了预览窗口中的数据量，避免对电脑造成过重的负担，用户可以基于预览的数据建立查询，而不必加载整个数据集，这使得处理大型数据集成为可能。然而，当数据集中超出预览范围的部分出现错误时，这些错误在预览中是不可见的，只有在加载整个数据集时才会被发现。

  通过上图可以看到，“POS Hour”列中错误是因为这一行的数据为21:00 ，无法将其转为整数格式，那该如何修复呢？

  查看整个“POS Hour”列的数据，发现其显示的应该是小时数（整数），而不是时间格式。所以在数据类型转换之前，插入一个步骤，将这一列中所有的:00替换为空就行。

可以看到“ErrorData 中的错误”查询在默认情况下被创建为“仅限连接”查询，在处理完错误后可以将其删除。

  以这种方式修复错误的问题是，用户无法在预览窗口中看到效果，可以在查询中插入一个临时步骤，删除查询的前3,880 行。这意味着第一个错误会出现在第 2 行中，可以预览到.

四、在 Excel 和 Power BI 间迁移查询

4.1 复制查询

  以“第 04 章 示例文件/Simple Query Chain.xlsx”为例，显示【查询 & 连接】选项卡，双击右击“Raw Data”查询，在【主页】选项卡【数据源设置】中，更改文件路径为“第 01章 示例文件/Basic Import.csv”，然后关闭并上载查询，保存excel表格。此查询结构为：

4.1.1 Excel 到 Excel

1. 复制：选择【查询 & 连接】窗格中要复制的查询，右击并选择“复制”或使用快捷键 Ctrl+C
2. 粘贴：在目标工作簿中，打开【查询 & 连接】窗格，进行粘贴。
   • Power Query 将自动粘贴查询及其依赖的前序查询。下图显示只复制Salse查询时，其两个前序查询也被正确复制了。
   • 如果目标工作簿中已存在部分查询，Power Query 会创建新的查询副本并添加数字后缀以区分。

• 按住按住 Shift 键并单击可选择多个连续的查询，按住 Ctrl 键可选择多个非连续的查询。

4.1.2 Excel 到 Power BI

1. Excel 中复制查询后
2. 打开 Power BI 文件，转到【主页】【转换数据】，然后右击【查询】导航器中的空白区域并选择“粘贴”。

  如查询是连接到外部数据源的，这种复制方式是OK的；如果查询依赖于 Excel 表，可能会遇到数据源无效的问题（Power BI 没有自己的工作表），此时应使用导入模式。

4.1.3 Power BI 到 Excel

1. 打开Power BI 文件，复制查询
2. 在 Excel 【查询 & 连接】窗格中，粘贴查询

  Power BI 比 Excel 包含更多的数据源连接器，此外Power BI 还支持自定义连接器。如果查询中使用了 Excel 不支持的数据源连接器，可能会出现步骤级错误，此时查询只能在Power BI中使用。下图显示了自定义“WooCommerce”连接器在 Excel 中不可用：

4.1.4 Power BI 到 Power BI

直接复制粘贴就行，如果在不同版本的 Power BI 之间复制，可能会遇到版本不兼容问题。

4.2 导入查询

  复制粘贴模式适用于在应用程序之间移动特定查询，而导入模式则用于导入整个解决方案，包括数据模型结构和度量值，下面是两种方式的对比：

	复制粘贴模式	导入模式
原始的 Excel 工作簿	必须为开启状态	必须为关闭状态
复制/导入特定的查询	支持	不支持
复制/导入所有查询	支持	支持
导入数据模型结构	不支持	支持
导入度量值	不支持	支持
连接到 Excel 中的表	不支持	支持，但会将数据复制

  当从 Excel 复制查询到 Power BI 时，如果查询引用了 Excel 工作簿中的表格（例如 Excel 表），会导致步骤级错误，因为 Power BI 无法直接识别 Excel 工作簿中的表格作为数据源。为了避免这种问题，Power BI 提供了“导入”功能，允许用户选择如何处理 Excel 中的表格数据。

  导入时有两种情况：仅导入外部数据源、导入整个数据模型。后者有两种导入模式：复制数据或保持连接。

4.2.1 仅导入外部数据源

  当导入的 Excel 查询仅依赖外部数据源时，Power BI 可以顺利导入查询，但此时查询只是连接状态。Power BI 不会自动加载在 Excel 中已加载的查询，需要手动启用加载设置。

  打开Power BI ，选择【文件】【导入】【Power Query , Power Pivot , Power View】，选择打开4.1章节中的“第 04 章 示例文件/Simple Query Chain.xlsx”，单击【启动】，开始导入过程。

  导入完成后，点击【应用更改】按钮，但 Power BI 数据模型中并没有生成任何表。原因在于 Power BI 和 Excel的兼容性问题。Power Query 查询被加载到 Excel工作簿之后，Power BI 无法直接识别 Excel 工作簿中 Power Query 加载后的内容，所以这些查询被自动设置为“连接”状态。任何没有加载到 Power Pivot 数据模型的 Excel 查询将只在 Power BI 中被设置为连接模式（仅识别查询的存在，但不执行数据加载）。要解决这个问题，需要在Power BI中启动Power Query，右击“Sales”查询，勾选【启用加载】选项。

4.2.2 导入数据模型

  打开“第 04 章 示例文件/Simple Model.xlsx”，此Excel通过 Power Query 和 Power Pivot 构建了一个完整的数据处理和分析解决方案：

1. 两个 Excel 表与12个查询：Data工作簿中有Raw Data - Sales 和 Raw Data - Budgets两个表，以及12个查询。
   
   打开Power Query，在视图-查询依赖项中可以看到这12个查询的依赖关系：
   

2. Power Pivot 数据模型：在数据->数据模型中单击 管理数据模型 选项，单击关系视图，可以看到此excel中的模型关系，其中包括四个指定的表、四个关系和两个度量值（Sales 和 Budget）：
   
   

3. 一个报表工作表：在Report工作簿中有一个基于 Power Pivot 数据模型的 PivotChart（透视图）和切片器，用于可视化分析。
   

  这是一个相当简单的基于 Power Query 和 Power Pivot 的解决方案。假设用户从同事那里拿到了这个 Excel 文件（Simple Model.xlsx），并需要将整个解决方案迁移到 Power BI 中，有两种方式：

• 手动复制查询：直接将查询从 Excel 复制到 Power BI。这样虽然可以导入查询，但不会导入数据模型中的关系和度量值。而Excel中所有的数据、查询、数据模型和 BI 报告都在同一个文件中，用户还不知道原同事建立它的所有逻辑，如果手动重新创建这些关系和度量值，可能非常繁琐。
• 导入整个解决方案：使用 Power BI 的导入功能，可以选择将整个 Excel 文件中的数据模型（包括查询、关系、度量值）导入到 Power BI 中，从而避免手动重建的麻烦。具体来说，有两种导入模式——复制数据与保持连接。但需要注意的是，导入后可能需要手动调整数据类型，特别是日期列。

4.2.2.1 导入时复制数据（默认）

  打开Power BI ，选择【文件】【导入】【Power Query , Power Pivot , Power View】，选择“第 04 章 示例文件/Simple Model.xlsx”，单击【启动】，将出现导入对话框。使用默认选项【复制数据】 ，这将启动查询和数据模型组件的导入。

  导入之后，切换到 Power BI 左侧的【模型视图】，可以看到数据模型结构，包括关系、度量值，甚至字段的可见/隐藏状态都已经正确导入。

转到 Power BI 的报告页面，可以快速复原 Excel 中的图表：

但是如果刷新数据，会弹出错误：

  为了解决这个问题，需要先检查查询。点击【转换数据】，打开Power Query，选择“Raw Data - Budgets”，可以看到日期列显示错误，变成了整数：

  选择“Source”步骤，单击齿轮图标，在这里看到的是 Power BI 在文件中创建的表（从 Excel 中复制数据的结果）。可以看到数据表的“Date”列中不包含日期，而是包含一列数值：

所以说在将 Excel 文件导入 Power BI 时，可能会遇到日期格式转换的问题。解决方式是：

1. 选择“Date”列，将数据类型改为【整数】。选择【替换当前转换】，这将清除之前的类型转换步骤，并用新的整数类型替换。
2. 再次选择“Date”列，将数据类型更改为【日期】。选择【添加新的步骤】，而不是【替换当前转换】，整数类型将被改为日期类型：
   

  正如上一章所说，一旦更改了数据类型，任何后续的更改都将基于这个输出。虽然不能将一个基于【文本】类型的数值改为【日期】类型，但可以将【文本】类型更改为值，然后将值类型更改为【日期】。

除了日期格式错误，使用这种方法也有一些潜在的危险

• 数据更新问题：如果需要对源数据进行更新，就必须在Power BI 查询中更新“Source”步骤。这个过程可能会比较繁琐，而且更新查询预览可能会很慢。
• 大小限制：Power BI 对导入的表的大小有限制，如果表超过了一定的大小，Power BI 将不允许用户进行进一步的修改。

4.2.2.2 保持连接（Keep Connection）

  另一种导入方式是与原始 Excel 文件保持连接，数据仍然存储在 Excel 文件中，Power BI 通过连接从 Excel 文件中读取数据。通过这种方式，Power BI 同样可以导入数据并创建数据模型、关系和度量值。

特性	复制数据	保持连接
数据便携性	数据完全包含在 Power BI 文件中，便于分享和分发，无需依赖外部文件。	Power BI 文件仅包含连接信息，需要确保 Excel 文件的路径正确
数据更新	需要在 Power BI 中编辑查询并手动修改数据源。过程繁琐且耗时。	只需在 Excel 文件中修改数据，然后在 Power BI 中刷新即可。
数据类型	可能会遇到日期等数据类型转换问题，需要手动修正。	无此问题
文件大小限制	存在文件大小限制	无此限制
适用场景	适合将数据完全迁移到 Power BI ，不需要频繁更新数据的场景。	适合需要定期更新数据的场景

虽然【复制数据】选项被推荐为默认选择，但【保持连接】才是一个更好的方法。

4.2.3 从 Power BI 导入到 Excel

  由于 Power BI 的数据模型版本比 Excel 的数据模型版本更新，并且支持许多新的功能，所以微软目前没有直接从 Power BI 导入到 Excel 的方法，不过可以使用如 Ken 的 Monkey Tools 插件来辅助完成这一过程。

  Monkey Tools 不能创建 Excel 数据模型不支持的项目，但可以有效重建查询结构、关系和度量值。它甚至还提供了一个未能正确导入的列表。无论用户只想提取和导入查询，还是整个数据模型，Monkey Tools 都可以轻松完成这一任务。

使用 Monkey Tools 将 Power BI 模型导入到 Excel 中

五、从平面文件（TXT/CSV）导入数据

  数据专家经常需要处理 TXT 和 CSV 文件，它们是典型的平面文件（缺少一个称为“Schema”的元数据层，用于描述文件内容的信息）。将平面文件导入 Excel 或 Power BI 时必须进行解析，所以了解数据解析时的默认操作及如何控制修改这些操作非常重要。

5.1 设置系统默认值

  Power Query 会基于 Windows 控制面板中的区域设置来解析数据，涉及日期、数字格式等方面，所以首先需要确认或正确配置用户当前的区域设置。按 Win 键进入控制面板，如果【控制面板】【查看方式】是【类别】视图，则单击【更改日期、时间或数字格式】；如果【控制面板】【查看方式】是【图标】视图，则单击【区域】：

  用户可以在这里了解整个计算机系统所使用的默认值。在这里将会看到，系统被设置为以 ISO 格式【yyyy-MM-dd】显示有效日期，此外，还对负数格式进行了自定义设置。

• 与 Excel 不同，Power Query 是区分大小写的。MM 用于表示月，mm 用于表示分钟。
• 日期格式主要分为以下几种：
  1. 日/月/年（dd/mm/yy）：英国、法国、意大利等
  2. 月/日/年（mm/dd/yy）：美国与部分加拿大地区
  3. 年/月/日（yyyy/mm/dd）：中日韩、德国、瑞典等

  这些设置是基于本地计算机的，因此不同用户的计算机可能会有不同的区域设置。当你在 Power Query 中导入数据并声明某一列的数据类型（如日期或货币）时，Power Query 会根据 Windows 控制面板中的“区域”设置来解析和显示这些数据。

5.2 程序如何解析平面数据

程序在解析数据时，需要知道如下三件事：

1. 数据点的分隔方式，是单个字符（分隔符）、一组字符还是根据固定的宽度；
2. 两个完整的记录（段落？）之间由什么分割（字符？字符列？）
3. 每个数据单元的数据类型是什么

  平面文件的问题在于缺少元数据（没有包含定义这些内容的信息），导致导入程序必须自行分析和猜测，以试图获得正确的结果。尤其是数据类型的推断，常常因为区域设置不同而出现错误。

  比如对于一个文本"1/8/18" ，可能被解释为 2018 年 1 月 8 日，2018 年 8 月 1 日，2001 年 8 月18 日，因为平面文件中没有元数据来告诉用户这到底是哪种格式，程序只能基于系统默认值（区域设置）进行猜测。

  假设以下数据集使用 MM/dd/yy 格式，但用户的区域设置为 dd/MM/yyyy短日期格式，使用.作为十进制分隔符，,作为数字千分位分隔符，将其导入Excel中会发现：

1. 程序试图将 1/8/18 转换为【dd/MM/yyyy】的日期格式，这样就生成了一个日期序列号为 43313（自 1900 年 1 月 1 日以来的天数）的整数值，将其进行日期格式化，结果为8/1/2018；
2. 程序试图将 1/13/18 转换为【dd/MM/yyyy】的日期格式，但由于没有 13 个月，它认为这不可能是一个日期，因此该数据视为文本，并在后续日期格式化中保持不变。
3. 数字45.67 和1,234.56按照系统区域设置的默认值来解析和格式化。

  在此过程中，一旦数据被错误地解释并存储（比如1/8/18 被解析为43313），后续更改将非常困难，甚至用户都意识不到，这便是从平面文件导入 Excel 等工具时常见的问题。除了日期格式，数字和货币也是一样，因为不同的国家使用不同的货币指标和数字分隔符。

   Power Query能够有效解决平面文件因缺乏元数据而导致的解析难题，除了传统导入逻辑（基于默认值），它还允许用户使用指定的区域设置，以明确数据该如何解析。

5.3 导入带分隔符的平面文件（使用指定区域设置解析数据）

  “CSV”（以逗号分割数据）或带分隔符的“TXT”文件的导入过程是非常简单的，并且遵循基本的 ETL 过程：提取、转换和加载数据。难点在于处理来自不同地区和具有不同格式的数据。

  以“Ch05-Delimited.CSV”为例，用记事本打开可以看到其数据以逗号分割。该文件包含具有挑战性的数据，如特定格式的日期和货币符号：

  文件中的日期格式假设为【MM/dd/yy】，将此文件导入Excel，可以得到：

  Power Query 会尝试解析数据类型，使用【控制面板】的【区域】设置来识别这些数据元素。读者显示的数据和数值可能与这里显示的不同，我的excel365打开csv显示的是：

  在我的电脑上，无论怎么设置，比如显式设置数据自定义格式为m/d/Y，或显式地将2012/替换为12/都没用，12月之前的数据都自动转为2012/m/d的格式，这样12月已经确定的转为2012年了，是不可能再转为月份的，用记事本打开也是这样显示。我想到的办法是将2012/转为12,，让它没办法自动解析为日期格式。

  文件中的日期格式为【MM/dd/yy】，即第一行表示 2008 年 12 月 1 日，但是用户区域设置默认以【yyyy-MM-dd】格式显示日期，所以被错误的解读为 2012 年 1 月 8 日。要更正这一点，需要先删除默认的“Changed Type”步骤并重新创建，创建时明确设置Power Query 该如何解析数据，这样无论谁打开这份文件，数据都将被正确解析。

  删除“Changed Type”步骤后回到“Promoted Headers”步骤，单击“Date”列顶部的【ABC】数据类型图标，选择【使用区域设置】，弹出以下对话框：

  选择【英语(美国)】，这是唯一遵循“M/d/y”标准的国家（对于我而言，区域设置之前需要将12,转为12/）。

  【使用区域设置】转换的整个目标是告诉 Power Query 如何解释一个基于文本的值，并将文本转换为正确的数据类型。

  使用同样的方式设置货币格式：【使用区域设置】 更改“Amount”列的数据类型，将地区设置为【英语(加拿大)】：

  数据集中的每一列都可以使用不同的【区域设置】进行更改；根据区域设置更改数据类型是单独的步骤，不能与其它数据类型更改步骤合并，这使得用户处理不同地区的数据有了更大的灵活性与确定性。

  如果要设置整个工作表的区域设置，Excel选择【获取数据】 【查询选项】当前工作簿的【区域设置】：

  在 Power BI Desktop中，需要进入【文件】【选项】【选项和设置】，可以自由配置全局或当前文件层的【区域设置】选项。

5.4 导入不带分隔符的平面文件（清洗数据）

无分隔符文本文件（如“ASCII.TXT”）导入和清洗非常繁琐，常见问题包括：

1. 确定分割方式：字符按位置对齐，而不是按字符分隔，甚至有不一致的对齐方式
2. 清洗非打印字符（如换行符等）
3. 清洗重复的标题行。

  以“GL Jan-Mar.TXT”为例，鉴于整个文件非常混乱，无法预处理，导入Excel之后会看到 Power Query 将所有数据放在一个列中，且没有标题提升和数据转换步骤。

  一些行的末尾有“…”，表明该单元格中文本的数量超过了适合该单元格目前可以显示的数量。如果文字都挤在一起，可以到【视图】选项卡，确保勾选 【等宽字体】 和 【显示空白】 的选项，清洗这样的数据时，确保勾选上。

1. 删除无用行：前 10 行没有什么价值，可转到【主页】【删除行】【删除最前面几行】，填写“10”删除这些行。

2. 清洗数据：

   • 右击“Column1”，选择【转换】->【修整】，删除开头和结尾的空格。
   • 右击“Column1”，选择【转换】->【清除】，去除非打印字符。

   • Power Query 的【修整】功能与 Excel 的【修整】功能有所不同，前者的TRIM函数只修整开头和结尾的空格，后者的TRIM函数还会将数据中间所有重复的空格替换成一个空格。
   • Power Query 的【清除】功能（ CLEAN 函数）与Excel 中是一样的

3. 按位置拆分列：转到【主页】【拆分列】【按字符数】，设置分割方式为重复，字符数设为15效果较好：
   
   

4. 删除“Changed Type”步骤（只是都改为文本，没什么用），将第一行提升为标题

5. 删除垃圾行（日期）：向下滚动鼠标，会发现这个数据中有大量垃圾行，主要是来自文件中重复的列标题和分隔。
   
   将“Tran Date”列的改为日期数据类型，区域设置为英语（美国），转换后“Tran Date”列中出现错误和 null 值的行即为需剔除的垃圾行，右击该列选择删除错误行，在列筛选中取消勾选null。现在已经有了一个从上到下都是有效的日期的“Tran Date”列
   

6. 删除垃圾行（货币）：将“Tran Amount”列数据类型更改为货币，区域设置为英语（美国），删除错误行和筛选掉 null 值。

7. 删除垃圾列：通过【视图】选项卡打开【列质量】和【列分发】功能，可以看到第 3 列全为空白值，和第9列仅包含 null 值，将其删除。
   

8. 合并列：将最后四列进行合并（无需分隔符）
   

9. 拆分列：可以明显看到，合并后的这一列是多个列以“-”为分隔符合并在一起的，所以使用“-”分隔符进行拆分，将其重命名为“Category”列和“Vendor”列。
   

10. 修剪重复的空格：通过替换值功能，将“Vendor”列中单词间的重复空格换为一个空格。如果怀疑没有处理干净，可重复多次

11. 上载数据：将查询名称改为“Transactions”，关闭并上载数据。

  现在数据已经清洗干净，可以创建数据透视表了。行字段为“Date”，按月分组，并加入“Vendor”字段；列字段为“Category”，值为“Amount”：

转到【获取数据】【数据源设置】，更改数据源为"GL Apr-Jun.TXT”，刷新数据可以得到：

六、 从Excel导入数据

在 Excel 中，数据可以有多种存储形式，这些形式会影响数据的引用和处理方式：

• Excel 表（Table）：通过 Ctrl + T 创建的结构化对象，具有固定的标题行和格式化功能。
• 区域（Range）：简单的矩形单元格范围，没有额外的结构化功能。
• 命名区域（Named Range）：对某个区域进行命名，方便通过名称引用。
• 动态区域（Dynamic Range）：通过 Excel 公式计算得出的单元格范围，可以根据数据的变化自动扩展。
• 工作表（Sheet）：Excel 工作簿中的一个页面，可以包含多个数据区域。

在处理 Excel 数据时，通常有两种数据导入方式：

• 从当前工作簿（活动）中导入数据：从当前打开的工作簿中导入数据时，Power Query 只能从两个地方读取： Excel 表（Table） 和 命名区域（包括动态命名区域）。Power Query 无法直接从普通的表格区域（未格式化为表）读取数据，但可以通过将普通区域转换为 Excel 表（按Ctrl + T），或定义命名区域（通过名称框）来解决。
• 从外部工作簿中导入数据：从另一个关闭的 Excel 文件中读取数据。

第 06 章 示例文件Excel Data.xlsx包含四个工作表，每个工作表包含相同的数据，但以不同的形式存储：

• “Table”：数据已经格式化为一个名为“Sales”的表。
• “Unformatted”：数据以普通表格形式存在，但未应用表格样式。
• “NamedRange”：数据定义为命名区域。
• “Dynamic”：数据包含一个公式（在 H2 单元格中），用于动态扩展数据范围。

下面演示 Power Query 如何处理不同存储方式的数据。

6.1 从当前工作簿导入数据

6.1.1 连接到表

  打开Excel Data.xlsx文件，转到Table工作表，可以看到这些数据已经被格式化为一个漂亮的 Excel 表格。在表格中任意单元格点击，通过数据选项卡，【获取数据】→【自其他源】→【来自表格/区域】的步骤；或直接右击选择从表格/区域获取数据，即可进入 Power Query 编辑器并预览数据。

  此查询自动继承了数据源的名称：“Sales”。由于 Excel 表包含元数据信息，Power Query 能自动识别标题信息，所以无需额外的“Promoted Headers”步骤。

  之后，可根据需要更改列的数据类型，并为查询重命名以避免加载到工作表时的命名冲突。比如将“Date”列从日期/时间格式改为日期格式；将“Cost”列和Commission”列更改为货币格式，选择替换当前转换。

6.1.2 连接到区域

  若数据以未格式化为正式表格的区域形式存在，需先将该区域转换为表格格式，并为其指定一个有意义的名称。这样做的好处在于，能够通过名称快速定位数据范围，同时避免因默认表名（如 Table1）导致的查询中断问题。

  同样在表格中任意单元格右击，选择从表格/区域获取数据，这将弹出一个 Excel 表格创建的弹窗，提示用户确认表格的边界和数据集是否包括标题：

  如果用户单击【确定】，Excel 将把数据转换成一个表，并起一个默认的名称（如 Table1 ），然后立刻启动 Power Query 编辑器。原始名称被硬编码到查询中，当用户以后更改表名时，查询就会中断。

建议的做法是：

1. 创建表格：单击数据区域内的任何（单个）单元格，进入【开始】【套用表格格式】，选择一种颜色风格（如果用户对默认的蓝色没有意见，也可以按 CTRL+T ）

2. 命名表格：进入【表设计】选项卡，将【表名称】改为“SalesData”
   

3. 导入表格：接下来使用和上一章节同样的方式导入此表格

6.1.3 连接到命名区域

  将 Excel 数据以表（Table）或区域（Range）的形式导入 Power Query 是最常见的方法，但这两种方式都会锁定列标题，并且应用样式。为了避免上述问题，用户可以选择使用 命名区域（Named Range） 来导入数据

  Excel 表格通常会自动应用颜色带和其他格式化样式。虽然这些样式可以让表格看起来更美观，但有时用户可能不希望在数据源中应用这些样式.

1. 命名区域：转到“NamedRange”工作表，选择单元格“A5:F42”，在名称框，输入名称“Data”后按回车键。
   
2. 导入数据：在表格中任意单元格右击，选择从表格/区域获取数据；或者在名称框中通过下拉箭头选择“Data”，在【数据】选项卡中选择【获取数据】【自其他源】【来自表格/区域】。此时，Power Query 会自动识别标题行，并对列应用数据类型。
   
3. 同样的方式修改数据类型，将查询的名称更改为“FromNamedRange”。
4. 【关闭并上载至】【表】【新工作表】【确定】

6.1.4 通过公式连接到动态区域

  Excel 表中的数据经常会不断扩展，使用固定的命名区域就不太合适了，此时可考虑使用动态区域来解决。

1. 创建动态区域：选择“Dynamic”工作表，转到【公式】选项卡，【名称管理器】【新建】，将名称改为“DynamicRange”，并输入公式=Dynamic!$A$5:INDEX(Dynamic!$F:$F,MATCH(99^99,Dynamic!$A:$A))，可以使该区域随着数据的增长自动扩展。
   

2. 导入数据：在 Power Query 中，创建一个【空白查询】，在公式栏中输入= Excel.CurrentWorkbook ()，告诉Power Query导入这个工作簿中所有可以连接的 Excel 对象。
   

3. 展开动态区域数据：底部的是刚刚创建的“DynamicRange”对象，直接单击此Table，将下钻进此表格：
   

6.1.5 通过设置打印区域连接到工作表（sheet）

  Power Query 在从当前工作簿中导入数据时，不能直接读取整个工作表的内容。如果直接选择整个工作表（Sheet），Power Query 无法识别，因为它无法确定哪些单元格是实际的数据，哪些是空白或无关的内容。

  为了绕过这个限制，可以通过定义一个 打印区域（Print_Area）来间接实现从整个工作表中导入数据。

1. 选择数据：选择工作表中包含数据的范围（通常是整个数据区域）。

2. 设置打印区域：在菜单栏中选择 页面布局 选项卡，点击 打印区域，然后选择 设置打印区域。这个操作会在 Excel 的名称管理器中创建一个名为Print_Area 的命名区域：
   

3. 导入打印区域数据：通过名称框选择Print_Area，导入数据。
   

6.2 从外部工作簿导入数据

6.2.1 连接到文件

  在一个新的工作簿（或 Power BI 文件）中，通过【获取数据】→【来自文件】→【从 Excel 工作簿】的步骤，打开“External Workbook.xlsx”（确保该文件处于关闭状态）。这会弹出一个查询【导航】窗口，允许用户选择想导入的内容：

用户可以连接到以下对象：

• 表：（Sales）。
• 工作表：（Table 和 Unstructured）。
• 命名区域：（_xlnm.Print_Area 和 NamedRange）。

  所以没有动态区域（“DynamicName”）的选项，这是因为动态区域的范围是通过公式动态计算的，而 Power Query 在连接到外部文件时无法解析这些动态公式。

  通过勾选 选择多项 复选框，可以选择多个数据对象，Power Query 将为每个选择的数据对象创建一个独立的查询，每个查询都将创建一个单独的文件连接。虽然可以通过 数据源设置 对话框一次性更新这些连接，但这种方法还是不够高效。更好的方法是创建一个与文件的单个连接，然后通过引用该连接来提取所需的数据。

1. 连接文件：直接选择External Workbook.xlsx文件夹，右击选择转换数据；或者是单击选择该文件夹之后，选择右下角的转换选项
2. 重命名查询：将新查询的名称更改为“Excel File”。
   
   在此预览中：
   • “Name”列：显示了每个 Excel 对象的名称；
   • “Data”列：显示的是“Table”表，可以单击钻取 ；
   • “Item”列：显示了对象名称的更详细的表示（包括打印区域的工作表名称）；
   • “Kind”列：显示数据列中的表包含的是哪种对象；
   • “Hidden”列：告诉用户该对象是否可见。

6.2.2 连接到表

1. 引用文件查询：右击“Excel File”查询，【引用】，重命名为“Table”
2. 展开“Sales”表：单击 Sales 表的 Table 关键字，这将使 Power Query 下钻到 Sales 表的具体内容。
   
   可以看到，从外部工作簿中导入的表与从同一工作簿中导入的表的处理方式非常相似，同样可以看到 Sales 表的数据预览，并且它将“Date”显示为【日期】而非【日期/时间】数据类型。
   

当连接到外部工作簿时，Power Query 的处理顺序是：

• 先连接到外部文件的路径
• 然后导航到用户选择的对象（如表、命名区域等）
• 最后连接到工作簿中的具体数据

6.2.3 连接到命名区域

1. 右击“Excel File”查询，【引用】，将其重命名为“Named Range”
2. 单击“NamedRange”表的“Table”关键字，下钻到命名区域
   

  命名区域通常是一个简单的矩形单元格范围，没有被格式化为正式的 Excel 表，因此，Power Query 会假设命名区域的第一行是标题行，然后尝试根据数据的内容自动设置每一列的数据类型。这些处理步骤与从当前工作簿导入命名区域类似。

6.2.4 连接到工作表

1. 右击“Excel File”查询，【引用】，将其重命名为“Worksheet”
2. 单击“Unstructured”表的“Table”关键字，下钻到工作表数据
   

  可以看到，预览中出现包含大量“null”值的情况，这是因为 Power Query 会连接到工作表的整个数据区域，包括空白单元格。因此，需要对数据进行清洗，如删除空白行、设置正确的标题行等操作，以确保数据的准确性和整洁性。

1. 删除不必要的步骤——Changed Type 和 Promoted Headers
2. 删除垃圾行（前4行），然后将第一行用作标题行
3. 删除空列（Column7列）。为了避免硬编码可能导致的错误，更好的方式不是直接删除Column7列，而是选择前几列，然后删除其他列，确保 Power Query 查询更加健壮。

   • 在导入 Excel 工作表的数据时，可能会发现一些额外的列，这些列在原始 Excel 文件中并不存在，但在 Power Query 的预览窗口中却显示出来了，且这些列充满了 null 值。直接删除这些列会将这些列的名称硬编码到步骤中，如果原始 Excel 文件的数据结构发生变化（例如添加了新列、删除了列或重新设置了数据范围），容易会导致 Power Query 查询出错。
   • 删除其它列不要的一点是，如果源数据中创建了一个新的数据列，那么此方式也会将其删除，

4. 检测数据类型：选择所有列，并使用 Detect Data Types 功能来自动检测并转换数据类型
5. 删除空白行：选择数据集中的所有列，选择【主页】【删除行】【删除空行】。
6. 加载到数据表中：选择【关闭并上载至】【表】【新工作表】【确定】，然后删除“Excel File”查询，Excel File”查询将被自动设置为【仅限连接】。

特性	Excel 表格（Table）	工作表（Worksheet）
数据结构	结构化，有固定的列标题和数据类型。	可能包含未结构化的数据，数据格式和组织可能不一致。
动态扩展	可根据需要自动扩展行和列，不会破坏数据结构。	不支持，添加新数据可能需要手动调整数据范围。
Power Query 处理	更容易识别和处理，减少查询错误和维护成本。	如果工作表中包含额外的空白列或不相关的数据，Power Query 可能会将这些列导入查询中，导致数据混乱，识别和处理时更加困难，
列标题和数据类型	固定的列标题和数据类型，便于识别。	没有明确的列标题或数据类型，可能导致 Power Query 将列名硬编码到查询步骤中。
可维护性	高，表格的结构化特性使得 Power Query 能够更准确地识别和处理数据，也不太可能出现列名硬编码或数据类型错误的问题	低，数据结构变化（如添加或删除列）可能导致查询错误。
适用场景	适合需要频繁更新和维护的数据集。	适合数据结构相对固定，不需要频繁更新的场景。

以上就是在用户有选择的情况下，宁愿选择表格而不是工作表的原因。

6.3 总结与建议

  一般来说，基于 Excel 表（不是命名区域或工作表）构建解决方案更为便捷和易于维护。然而，在实际应用中，我们可能会遇到各种不同的数据存储情况，比如通过自动化创建文件时不能使用表，这就需要灵活运用上述各种连接方法。

  此外，在构建解决方案时，将数据源与业务逻辑分离（即将源数据放置在一个单独的 Excel 文件中）具有诸多优势：

• 支持多用户更新数据
• 便于解决方案的升级和共享。比如当数据增长到一定程度，需要移植到数据库中进行管理，只需要移动数据，并更新查询以指向新的源。
• 能够在同一个 Excel 数据源上构建多个报表解决方案
• 能够直接从工作表中读取数据

  这种方法不支持动态区域读取；需要为不同的用户管理和更新文件路径；在编辑查询时，无法共享修改同一套逻辑。尽管如此，从长远来看，对于复杂的数据处理和分析场景，将数据源与业务逻辑分开是一种更为合理和可持续的策略。

十一、基于 Web 的数据源

  在当今数字化时代，数据来源日益多元化，而Web作为最大的开放数据仓库，蕴含着海量的业务价值信息。Power Query 可以从 Web 上抓取与业务相关的数据，并用它来丰富自己的公司数据。从Web抓取数据有两种典型的场景：

1. 连接到Web托管的文件：如果数据以文件形式存储在Web上（如CSV、XLSX等格式），使用Power Query的"自网站"连接器可以轻松导入这些数据，操作过程与连接本地文件类似。

2. 直接抓取HTML网页数据：从HTML网页抓取数据更具挑战性，因为网页结构可能不一致。

   • Power Query提供了"自然表"和"建议表"功能，尝试从HTML文档中推断出表格结构。
   • 创新功能："使用示例添加表"允许用户通过输入示例数据引导Power Query自动填充整列，极大简化了复杂HTML结构的数据提取过程。

11.1 连接到Web文件

以https://data.cityofnewyork.us/api/views/c3uy-2p5r/files/fb52d9bb-0a7c-4cc4-824e-1930c818e5d1?download=true&filename=NYCCAS_Air_Quality_Indicators_Open_Data.xlsx为例，这是一个储在 Web 上的xlsx文件，可通过以下步骤进行连接：

1. 创建连接：转到【数据】选项卡，【获取数据】【自其他源】【自网站】

2. 输入URL：在【URL】字段中输入文件路径并单击【确定】
   

3. 身份验证：如果用户以前没有连接到此网站，则会提示用户选择适当的身份验证方法。由于cityofnewyork.us 网站提供了大量可无需身份验证就可以读取的开放数据，所以此处选择【匿名】方式验证。

4. 数据预览：连接成功后，用户将看到与连接到本地 Excel 文件完全相同的数据预览界面
   

  第一次使用 Power Query 连接到 Web 数据或数据库时，系统会弹出身份验证框，要求您输入凭据（如用户名和密码）。这些凭据在第一次设置后会被缓存，因此在后续的连接中，系统会自动使用缓存的凭据进行身份验证。如需更改验证信息，详见本文12.1.2章节：管理凭据。

11.2 连接到 HTML 网页

  假设用户想从纽约市网站获取开放数据集列表，可使用Power Query连接到相应的URL——https://data.cityofnewyork.us/browse?limitTo=datasets。此页面本身包含一个数据集列表，每条都是一个数据集的链接及其相关信息：

11.2.1 连接到网页

  连接过程与连接Web文件相同，即通过"获取数据"→"自其他源"→"自网站"，并选择"匿名"身份验证。

  由于网站数据可能发生变更，如果此页面格式可能会发生改变，或者 URL 被弃用，可以打开**“第 11 章 示例文件/NYC Open Data.html”**，然后从 Web 浏览器复制该路径，并使用它来代替上面的 URL。

11.2.2 自然表和建议表

  打开此页面可以发现，该页面不存在任何已定义的表。如果有，在【导航器】窗口的左侧窗格中将看到标题为"HTML表格"的列表；相反，在这里看到的只是两个"建议的表格"。

  Power Query会尝试识别网页中的表格结构，若页面包含标准HTML表格，则直接显示为"自然表"；对于非标准表格，会基于CSS布局推断生成"建议表"。

  用户还可以单击预览区域上方的 【Web 视图】，切换到基于 Web 的数据预览，将网页内容与 Power Query 所做的表格推断进行比较，验证数据提取准确性。

11.2.3 使用示例添加表（Add Table Using Examples）

  “使用示例添加表” 允许用户通过提供示例数据来指导Power Query如何从网页中提取和解释数据。此方式将根据用户输入的示例数据使用机器学习算法自动填充整列，使得用户能够更精确地控制数据提取过程，适用于复杂HTML结构的精细数据提取。

• 网页结构复杂或不规则：当网页不包含明确的表格标记或者表格结构复杂，Power Query无法自动识别数据时，用户可以通过提供示例来帮助Power Query理解数据的结构和内容。
• 需要精细控制数据提取：用户可能需要从网页中提取特定的信息，而这些信息并不是以标准表格形式呈现的。通过"使用示例添加表"，用户可以精确指定需要提取的数据部分。
• 动态网页内容：对于动态生成的内容，如通过JavaScript加载的数据，传统的表格识别可能无法工作，这时可以通过示例来指导Power Query提取数据。

  首先在Power Query的导航器中单击"使用示例添加表"按钮，这将进入一个新的名为【使用示例添加表】的用户界面，该界面顶部显示数据预览，底部显示空列。用户在第一列的第一个单元格中输入要提取的内容的示例。Power Query会尝试理解用户的意图，并自动填充这一列的其他部分。

  使用这个功能时，根据经验会发现"少即是多"的说法是正确的。键入要提取的部分数据（如DOB），然后双击或选择下拉列表中高亮显示的文本（预期匹配的文本），接着按 Enter 键进行确认。短暂延迟后，Power Query 会根据用户的示例输入信息以及其他网页上的数据推断出用户真实的提取意图，并自动填充这一列的其他部分。

  用户可以继续为其他列提供示例，或者让Power Query根据已有的示例自动推断。完成后，用户可以直接【加载】或者【转换数据】：

  如果Power Query的推断结果不准确，只需要重新输入该条目并覆盖 Power Query 的原始推测结果即可，不断优化整个列的提取结果。如果示例输入不当，可能会导致大量空值，表明Power Query无法确定正确的提取逻辑。因此，正确使用这个功能需要一定的试验和调整。

11.3 连接到没有表的页面

11.3.1 原理解析

  当用户在Power Query中尝试连接到一个网页，但发现无法直接显示或识别出所需的表格数据时，这通常意味着网页不包含任何已定义的表格标记，或者Power Query无法自动推断出表格结构。例如对于网页https://data.cityofnewyork.us/Housing-Development/DOB-Job-ApplicationFilings/ic3t-wcy2，连接后生成如下预览窗口：

• 要查找的数据表没有显示，无论是自然HTML表还是建议表
• 无法使用【使用示例添加表】功能创建表格。

为了解决这个问题，用户需要使用Web浏览器的开发者工具 来进行手动查找和定位。

1. 输入以上URL打开网页，或者打开示例文件中的DOB Job Application Filings.html， 按 F12 打开开发人员工具
2. 单击【元素检查器】按钮（位于【开发人员工具】窗口的左上角）或按 Ctrl+Shift+C
3. 将鼠标悬停在页面上，突出显示所需元素；单击Dataset Information区域，在【元素】窗口中选择该元素
   通过在Power Query的"导航器"窗口中逐层钻取HTML元素，直到找到包含所需数据的部分，期望可以提取出如下数据：

11.3.2 难点解析

  选择"获取数据"→"自其他源"→"自网站"，然后输入目标网页的URL，选择"转换数据"以打开Power Query编辑器。

• "Name"字段包含你在Web开发者工具中显示的元素（Head 和 Body 标签）
• 使用"导航器"窗口中的"Children"列来逐层钻取HTML元素，直到找到包含所需数据的部分。

整个钻取过程可能会非常复杂，包括：

• 缺乏直观的HTML标签名称：HTML 中的标签都有名称，但在 Power Query 中用户看不到它们，这使得用户很容易迷失
• 钻取步骤不明确：Power Query的"应用的步骤"窗口不会详细记录每一步的路径，而是将所有步骤合并在一起，这使得用户在发现错误时难以回退到上一个正确的步骤。一旦用户发觉路线出错，唯一的办法就是从头开始。
• 数据格式不一致：在导航过程结束时，可能会发现表格的列格式不一致，比如下图中，表格的一列显示为原始文本，另一列则包装在特定元素中，这需要进行额外的数据清理和格式化操作。
  

  将最终导航的结果转为一个干净的表超出了本章的范围，因此现在不讨论这种方法。但是，完成此过程的步骤已保存在示例 “第 11 章 示例文件\From Web–The Hard Way.xlsx” 中，此查询已另存为TheHardWay。

11.3.3 钻取步骤

  从上图初始表开始，严格地遵循以下步骤进行钻取，用户将钻取到"TheHardWay"查询的导航步骤中同的位置，并且可以按照其余步骤一直钻到最后。

1. HTML （第 1 行）。
2. Body （第 3 排）。
3. Main （第 6 排）。
4. DIV（第 4 排） • DIV（第 2 排） • DIV（第 1 排） • DIV（第 2 排）。
5. DIV （第 1 行）。
6. SECTION （第 1 行）。
7. DIV （第 2 排） • DIV （第 2 排）。
8. DIV（第 2 排）。
9. TABLE （第 2 行）。
10. TBODY（第 1 排）。
11. TR （第 1 排）。

  请注意，这个过程可能需要多次尝试和错误，特别是对于不熟悉HTML结构的用户。此外，网页的结构可能会随时间变化，因此可能需要定期更新数据提取步骤。

11.4 从 Web 获取数据的注意事项

1. 数据稳定性风险： 网站结构变更可能导致查询失效（如页面改版、URL变化等），建议定期验证数据连接，并建立异常处理机制；

2. 数据完整性挑战：当我们从网站上获取数据时，需要特别注意数据的来源和准确性。
   以维基百科为例，虽然它是一个内容丰富、信息量巨大的网站，但它的数据是由普通用户编辑和贡献的，这意味着任何人都可以修改上面的信息。这就导致了一个问题：维基百科上的信息可能不是完全准确的，因为它们可能被错误地编辑或更新。

3. 数据更新：网站内容可能没有及时更新，当【刷新】数据时，没有获得最新数据

4. 技术限制：对于采用优化网页加载技术（如延迟加载内容）的网站，Power Query可能无法获取完整的页面数据，因为它可能在页面完全加载之前就确定了页面结构。可考虑使用API接口（若存在）或结合Power Automate实现页面完全加载后的数据抓取

11.5 总结

  Power Query为数据分析师提供了强大的Web数据集成能力，但其有效性高度依赖于目标网页的结构设计。对于具有良好HTML语义化的页面，数据提取过程可实现自动化与可视化；而对于复杂或动态加载的页面，则需要结合开发者工具进行精细的手动定位。

未来发展趋势与建议：

• Power BI中的Web连接器增强功能（如改进的表格推断算法）有望逐步同步至Excel
• 建议关注Power Query团队的更新日志，及时采用新功能简化数据集成流程
• 建议学习HTML/CSS基础结构知识，增强复杂场景应对能力