Power Query 真经 - 第 11 章 - 处理基于 Web 的数据源
Power Query 真经 - 第 11 章 - 处理基于 Web 的数据源
使用 Power Query 的一个非常有趣的场景是，可以利用它从 Web 上抓取与业务相关的数据，并用它来丰富自己的公司数据。数据通常以两种不同的方式之一存储在 Web 上。
- 存储在网站中的文件。
- 基于 HTML 的网页。
只要数据存储在 Power Query 理解的格式（“CSV”，“XLSX” 等）中，那么从它们中提取数据是相当容易的。然而，后者可能更具挑战性，因为页面可能包含或可能不包含一致的结构。Power Query 团队一直在研究这个功能，在编写本篇时，【网页连接器基础结构更新】功能已发布在 Power BI 的预览功能中，用来解决这个问题。
11.1 连接到 Web 数据文件
假设用户在 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
尽管这是一个 “xlsx” 文件，但系统不会使用 Excel 连接器提取它，因为它存储在 Web 上，而不是计算机上的本地文件夹。相反，用户将使用【自网站】的连接器，步骤如下，结果将如图 11-1 所示。
- 转到【数据】选项卡，【获取数据】【自其他源】【自网站】。
- 在【URL】字段中输入文件路径并单击【确定】。
图 11-1 连接到 Web 上托管的 Excel 文件
如果用户以前没有连接到网站，则会提示用户选择适当的身份验证方法。
【注意】 cityofnewyork.us 网站提供了大量可无需身份验证就可以读取的开放数据。在连接到此源时选择【匿名】。
清除身份验证方法后，用户将看到与连接到本地 Excel 文件完全相同的体验，如图 11-2 所示。
图 11-2 和连接到本地 Excel 文件有差别吗
这是 Power Query 团队设计这个软件的一致性。虽然连接器有所不同，但该过程的其余部分与处理存储在本地的文件相同。出于这个原因，在这里实际上不会对这个数据集执行任何转换，重要的是，用户需要认识到连接到存储在 Web 上的文件并从中导入数据是很容易的。
11.2 连接到 HTML 网页
假设在这个场景中，用户希望从纽约市网站上获取所有开放数据集的列表。用户发现的用于查找此信息的 URL 为：
https://data.cityofnewyork.us/browse?limitTo=datasets
【注意】 由于该网站不属于本书作者，因此在本书发行到用户阅读本章之间，页面格式可能会发生改变，或者 URL 可能会被弃用。如果发生这种情况，本书作者发行时已经在示例文件中保存了页面的副本。只需双击 “NYC Open Data.html” 文件，从 Web 浏览器复制该路径，并使用它来代替上面的 URL。
页面本身似乎包含一个数据集表，其中包含关于每个数据集的一些相关信息，如图 11-3 所示。
图 11-3 来自纽约【OpenData】的数据显示在 Microsoft Edge 浏览器中
11.2.1 连接到网页
【注意】 在撰写本文时，【网页连接器基础结构更新】功能仍然是 Power BI 桌面中的【预览功能】，根本没有在 Excel 中发布。如果用户的【导航器】屏幕与下面显示的屏幕不同，则表示用户尚未收到这个更新。在这种情况下，用户将看到本章 “连接到没有表的页面” 部分中显示的界面。
首先，连接到网页的方式与连接到 Web 文件的方式相同。
- 【获取数据】【自其他源】【自网站】。
- 如果出现提示，请选择【匿名】身份验证（针对此数据集）。
用户将再次被带到【导航器】窗口，但这一次会注意到，还有更多选项可供选择，如图 11-4 所示。
图 11-4 使用 Power Query 连接到 HTML 网页
11.2.2 自然表和建议表
关于这个网页，用户可以立即认识到的一点是，它实际上并不包含任何已定义的表。如果有，将在【导航器】窗口的左侧窗格中看到标题为 “HTML 表格” 的列表。相反，在这里看到的只是两个 “建议的表格”，Power Query 引擎从 HTML 文档的 CSS 推断出的表。
在【导航器】列表中选择 “Table1” 后，Power Query 将显示它所定义的表的预览。如果用户想要对这个数据进行转换，则可以勾选 “Table1” 前面的复选框，然后是在【加载】和【转换数据】之间做出一个选择。
用户还可以切换到基于 Web 的数据预览，允许用户将完全格式化的网页与 Power Query 所做的表格推断进行比较。要执行此操作，请单击预览区域上方的【Web 视图】，如图 11-5 所示。
图 11-5 在【Web 视图】中预览网页
通常，用户将使用此视图来快速比较推断出的表数据与 Web 视图中的数据。请注意，在【导航器】中选择表不会以任何方式突出显示或更改【Web 视图】，因此在选择【加载】前，可以切换回【表视图】查看。
11.2.3 使用示例添加表
但是，如果用户想要更多地控制数据的解释方式，该怎么办？这就是【使用示例添加表】变得非常有用的地方。单击该按钮，将进入一个新的名为【使用示例添加表】的用户界面，该界面顶部显示数据预览，底部显示空列。这里的目的是选择第 1 列中的第一个单元格，输入要为第一条记录提取的内容，Power Query 将完成其余工作，如图 11-6 所示。
图 11-6 图 11-6 使用【示例添加表】获取数据集标题
使用这个功能时，根据经验会发现 “少即是多” 的说法是正确的。键入要提取的部分数据，然后双击或选择高亮显示的文本并按 Enter 键选择与预期值匹配的文本。短暂延迟后，Power Query 会根据用户的示例输入信息以及其他网页上的数据推断出用户真实的提取意图，并自动填充这一列的其他部分。如果用户发现有个别条目不正确，只需要重新输入该条目并覆盖 Power Query 的原始推测结果即可，这样可以优化整个列的提取结果。
【警告】 如果用户的 “示例输入” 导致显示大量空值，则表示 Power Query 无法确定提取值的正确逻辑。
完成第一列后，双击列标题将其重命名，如果要添加更多列，请单击 “+” 图标。在如图 11-7 所示的视图中，可以构建了一个表，根据第一列的记录，从其中提取的内容包括数据集，浏览次数，以及最后更新时间。
图 11-7 使用【示例添加表】获取数据
完成后，用户可以通过单击【确定】，然后选择进一步【加载】或【转换数据】来访问自定义表，如图 11-8 所示。
图 11-8 将自动选择 “自定义表” 进行【加载】
11.3 连接到没有表的页面
若本书出版之际，【使用示例添加表】的功能尚未在 Excel 中发布，只能耐心再等待下。如果情况是网页不包含已定义的表标记，那么用户将有可能经历尝试深入 HTML 元素内部的可怕体验。这种体验就像在地下迷宫中使用蜡烛照明一样有趣，每个路标上都简单地写着 “从这条路出去”。
获得帮助的最佳途径是打开 Web 浏览器，打开开发人员工具，并尝试查找要提取的元素。对于这个例子，此时将考虑以下网页：
https://data.cityofnewyork.us/Housing-Development/DOB-Job-ApplicationFilings/ic3t-wcy2
当前的目标是从网页中提取如图 11-9 中的数据。
图 11-9 此表不显示在预览窗口中
【注意】 虽然 Power BI 的新 Web 表推断功能中标识了该表，但在撰写本文时，该特性并未出现在 Excel 的连接器中。即使使用了新的连接器，也可能会出现类似的情况，因此用户需要探索如何通过 Power Query 浏览 HTML 文档结构。当然，探索这种复杂需要勇气。
那么用户怎么知道自己被迫掉入了这个兔子洞呢？当用户发现以下两个条件都被满足的时候。
1. 要查找的表不会显示（无论是自然 HTML 表还是建议的表）。
2. 无法使用【使用示例添加表】功能创建表格。
这个场景对于用户来说很容易在 Excel 中从 Web 获取数据时复现，因为目前 Excel 并没有上述的高级接口。
连接到网页将产生如图 11-10 所示的预览。
图 11-10 只存在四个 HTML 表缺少用户想要的一个
要在 Microsoft Edge 或 Chrome 中找到所需元素的路径，用户需要转到页面并按 F12 展开开发人员工具，如图 11-11 所示。
图 11-11 浏览 HTML 界面
找到元素的诀窍如下。
- 单击【元素检查器】按钮（位于【开发人员工具】窗口的左上角）或按 Ctrl+Shift+C。
- 将鼠标悬停在页面上，突出显示所需元素。
- 单击它，在【元素】窗口中选择该元素。
一旦用户这样做了，用户就可以开始痛苦的第二部分；在 Power Query 中重复刚刚寻找表格元素的步骤。
- 创建新查询【自其他源】【自网站】输入【URL】：
https://data.cityofnewyork.us/Housing-Development/DOB-Job-ApplicationFilings/ic3t-wcy2
- 单击【确定】选择文件【转换数据】。
现在，用户将在 Power Query 界面看到一个相当不友好的视图，如图 11-12 所示。
图 11-12 最令人失望的结果
现在，用户需要非常仔细地复制在 Web 开发人员界面中执行的步骤，钻取 Power Query 的相应表元素。这两个程序之间有一些相似之处，但即便如此，也很容易迷失方向。
导航此过程的诀窍是识别 Power Query 中的 “Name” 字段包含 Web developer 工具中显示的元素。在本例中，这里有 HTML ，在浏览器的顶部可以看到 <HTML 类，这两项是相同的，如图 11-13 所示。
单击 “Children” 列中的表格以深入查看。
图 11-13HTML 元素的子元素
现在看到 Head 和 Body 标签。基于用户扩展的 HTML ，此时需要深入到 Body 标记中。用户会单击那里的表格，然后继续。
这个过程的问题在于，HTML 中的标签都有名称，但在 Power Query 中用户看不到它们，这使得用户很容易迷失。此外，【应用的步骤】窗口不会逐步记录用户的路径，它只是将所有步骤合并在一起，让用户无法回退到上一个步骤。一旦用户发觉路线出错，唯一的办法就是从头开始。
似乎这不是问题的最糟糕部分，在导航过程结束时，表格的一列显示为原始文本，另一列包装在 元素中，这意味着需要进行额外的操作，如图 11-14 所示。
图 11-14 这并没有变得更容易，甚至表格列格式也不一致
由于将其放入一个干净的表中的步骤超出了本章的范围，因此现在不讨论这种方法。但是，完成此过程的步骤已保存在已完成的示例中，可在 “第 11 章 示例文件 \From Web–The Hard Way.xlsx” 中找到。此特定查询已另存为 “TheHardWay”。即使要查看该查询，用户也需要认识到导航步骤是按照下面的文档生成的。
从初始表开始，如图 11-15 所示。
图 11-15 在 “Children” 表中钻取
- HTML （第 1 行）。
- Body （第 3 排）。
- Main （第 6 排）。
- DIV（第 4 排）・DIV（第 2 排）・DIV（第 1 排）・DIV（第 2 排）。
- DIV （第 1 行）。
- SECTION （第 1 行）。
- DIV （第 2 排）・DIV （第 2 排）。
- DIV（第 2 排）。
- TABLE （第 2 行）。
- TBODY（第 1 排）。
- TR （第 1 排）。
如果用户严格地遵循上述步骤，用户将钻取到 “TheHardWay” 查询的导航步骤中显示的完全相同的位置，并且可以按照其余步骤一直钻到最后。
现在应该能认识到：钻取 HTML 文档的工作理论上是的确可以完成的，这比其他方法要好。话虽如此，这并不适合缺乏耐心的人，而且整个过程可能会难以置信地令人沮丧。
11.4 从 Web 获取数据的注意事项
可以看出，从 Web 获取数据是 Power Query 的一个弱点。好消息是，根据本书在 Power BI 中展示的效果，未来情况将会好转（希望在用户阅读本书时，能在 Excel 中看到 Power BI 中的同样特性）。
但是，重要的是要认识到，即使有更好的连接器，在开发基于 Web 数据的解决方案时，仍有一些事情需要注意。
以下讨论并不是为了给出不要基于网站数据开发解决方案的理由，相反，它们旨在确保用户进入这一领域时更清楚：依赖用户无法控制的网络来源数据的好处和风险。
11.4.1 收集数据的经验
在 Power Query 中，针对 Web 数据构建解决方案可能是一个非常痛苦的过程。正如在前面的 Power BI 示例中所示，如果文档后面有表标记或设计良好的 CSS，那么该工具可以很好地工作。在这一点上，用户会看到自然的或建议的表格，事情很简单。然而，如果不是这样，所有的赌注都将失败，用户将被送上地狱之路。不幸的是，这远比没有表标签或 CSS 要更复杂，对于采用了优化网页加载技术的网站（如延迟加载内容）可能意味着 Power Query 抓取数据时看不到完整的页面，因为它在完全加载之前就确定了页面结构，Power Query 抓了个空。
希望 Power Query 团队将继续在这一领域开展工作，添加用户界面选项以增强体验，并希望永远不要再让人进入 HTML 地狱。
11.4.2 数据完整性
Web 数据的另一个主要问题是源和完整性。小心连接和导入来自维基百科等网站或其他与用户公司业务没有关系的网站的数据。
虽然可以将获取维基百科数据作为一个很好的例子，但现实是，依赖这个网站可能会很危险。其中的内容是精心策划的，但可由用户更改。尽管该网站尽了很大努力来整理数据，但网站上的信息还远远不够完美，可能包含的数据并不完全真实。
另一个问题是数据更新的容易程度。想象一下，花时间针对一个网页构建一个复杂的查询，却发现所有者 / 网站管理员没有及时更新它。用户需要确保，当【刷新】数据时，系统不仅刷新过去的数据，而且刷新最新的数据。在这里，用户已经投入了大量的时间，并在假设上次刷新时提取了最新数据的情况下做出了业务决策。
11.4.3 解决办法稳定性
由于网站不受到用户控制，这必然导致一个非常现实的问题：任何公司都一样，为了更好地为客户服务，各种页面的内容都可能发生改变。而问题是，网站可能并不会保持始终如一的结构和不变的体验，事实往往还正好相反：网站经常更新东西，改变现有这些网页并添加新的内容，或使网站做的更酷。这显然会导致一个副作用，那就是在没有任何通知的情况下，引用该网站数据的查询程序不再可用，使用者也恰好可能没有时间修复已经不可用的查询。
正在学习 Power Query 吗？本系列足以。