实验室 01 - 使用 Excel 中的 Power Query
现在,你可以通过此引导式的分步用例来试用 Power Query。 使用提供的示例数据源文件完成这些练习。
实验室 01 - Excel 中的分析:使用 Excel 中的 Power Query
完成此实验预计需要 30 分钟。
在此实践学习实验中,你将完成以下任务:
- 使用 Power Query 连接到 .csv 源数据文件 - 客户
- 使用 Power Query 转换按分隔符拆分列 - 客户
- 使用 Power Query 连接到 Excel 源数据文件 - 报价
- 使用 Power Query 转换到逆透视 - 报价
- 使用 Power Query 转换到清理 - 报价
实验先决条件
必须满足以下先决条件和设置,才能成功完成练习:
必须连接到 Internet。
必须安装了 Microsoft Office。
至少有一台具有两个核心和 4 GB RAM 的计算机,该计算机运行 Windows 8 或更高版本,或者 Windows Server 2008 R2 或更高版本。
可使用 Microsoft Edge 或 Google Chrome。
请验证操作系统是 32 位还是 64 位,以便确定是安装 32 位还是 64 位应用程序。
注意
64 位 Excel 和 Power BI Desktop 是最佳选择。
重要
下载学生内容:在本地计算机的 C: 驱动器上创建一个名为 ANALYST-LABS 的文件夹。 从 https://aka.ms/modern-analytics-labs 下载所有内容并将其提取到你创建的 ANALYST-LABS 文件夹中 (C:\ANALYST-LABS)。
使用以下任一选项下载并安装 Power BI Desktop:
- 如果有 Windows 10 或更高版本,请使用 Microsoft App Store 下载并安装 Power BI Desktop 应用程序。
- 下载并安装 Microsoft Power BI Desktop。
如果已安装 Power BI Desktop,请确保已下载最新版本的 Power BI。
文档结构
每个实验室的源数据或启动文件均位于每个实验室文件夹内。
使用 Excel 应用程序中的 Power Query 完成实验室 01。
使用 Power BI Desktop 应用程序完成实验室 02A 和实验室 02B。
使用 Power BI Desktop、Power BI 服务和 Excel 应用程序完成实验室 03A。
使用 Excel 和 Power BI 服务应用程序完成实验室 03B。
每个实验室都附带要遵循的分步说明,并在整个说明中包含屏幕图像。 每个步骤的关键操作都用粗体文本标识。 注意备注、提示和其他重要信息。 每个实验室都包含一个完整的解决方案文件,你可将其作为参考。
概述
完成此实验预计需要 30 分钟。 在此实验室中,你将完成以下任务:
使用 Power Query 连接到 CSV 源数据文件 - 客户
使用 Power Query 转换按分隔符拆分列 - 客户
使用 Power Query 连接到 XLSX 源数据文件 - 报价
使用 Power Query 转换到逆透视 - 报价
使用 Power Query 转换到清理 - 报价
注意
此实验室是根据虚构的 Wi-Fi 公司 SureWi 的销售活动创建的,它由 P3 Adaptive 提供。 这些数据是 P3 Adaptive 的资产,并且已共享,用于通过行业示例数据来演示 Excel 和 Power BI 的功能。 对此数据的任何使用都必须指明数据为 P3 Adaptive 所有。
练习 1:使用 Power Query 连接到 CSV - Customers.csv
在此练习中,你将使用 Excel 连接到 CSV 源数据文件。
任务 1:启动 Excel
在此任务中,你将启动一个新的空白工作表以开始。
启动 Excel。
创建一个新的空白工作簿。
任务 2:使用 Power Query 连接到 CSV
在此任务中,你将连接到客户 CSV 源数据文件。
选择主 Excel 功能区上的“数据”选项卡。
选择“获取数据”>“从文件”>“从文本/CSV”。
转到 C:\ANALYST-LABS\Lab 01\MAIAD Lab 01 - Data Source - Customers.csv 文件。
“预览”区域将显示客户数据、列名称和值的示例。
注意
此示例只是数据的预览。
选择“转换数据”按钮。 这将启动 Power Query 编辑器窗口。
注意
在 Power Query 中工作时,最好最大化“Power Query 编辑器”窗口,以便查看 Power Query 窗口菜单、窗格和选项的完整视图。
默认情况下,将折叠“Power Query 编辑器”窗口左侧的“查询”窗格。 选择“查询”窗格中的箭头,展开并打开“查询”窗格。
在“查询”窗格中,右键单击名为“MAIAD 实验室 01 - 数据源 - 客户”的默认查询名称,然后将查询重命名为“客户”。
提示
应为用作数据模型一部分的查询提供一个清晰的、描述性的、用户友好的名词性名称,用于描述数据所表示的内容。 例如,客户、报价、发票、产品、地理位置等。
练习 2:使用 Power Query 转换按分隔符拆分列 - 客户
在此练习中,你将使用 Power Query 从“Contact”列中提取名字。
任务 1:使用“从示例中添加列”
在此任务中,你将通过使用“添加列”>“示例中的列”转换创建名为“First Name”的新列,以通过分隔符来拆分“Contact”。
从“预览”网格中,选择“Contacts”列。
从“添加列”选项卡中选择“示例中的列”向下箭头,然后选择“从所选列添加列”选项。
注意
这将打开一个名为“从示例中添加列”的新用户界面窗口。此窗口看起来像 Power Query 的“预览”网格,但它是一个单独的窗口,你可以在其中键入建议的值,以便 Power Query 可以识别要应用的模式和公式,从而达到最终结果。
在“从示例中添加列”窗口的名为“[Column1]”的列中,键入值“Hugo”,然后按 Enter 键。
注意
按 Enter 键后,Power Query 会确定数据中是否存在一个模式来填充所有行的值。
在名为“分隔符前的文本”的默认标题中双击,并将新列重命名为“First Name”。 选择“确定”按钮。
注意
现在,在 Power Query 编辑器预览网格中,你会注意到名为“First Name”的新列,它是通过使用“从示例中添加列”转换从 [Contact] 解析出 [First Name] 而创建的。
练习 3:使用 Power Query 连接到 XLSX - Quotes.xlsx
在此练习中,你将使用 Excel 连接到 XLSX 源数据文件。
任务 1:从 Power Query 编辑器窗口中连接到 XLSX 源数据
在此任务中,你将从“Power Query 编辑器”窗口开始。
从 Power Query“主页”菜单中,选择“新建源”>“Excel 工作簿”文件选项。
导航到文件“C:\ANALYST-LABS\Lab 01\Data Source - Quotes.xlsx”。
在“导航器”窗口中,选择名为“实验室 01 - 报价”的工作表。
注意
这只是数据的预览。
选择“确定”按钮,作为“Power Query 编辑器”窗口中的第二个查询加载。
在“查询”窗格中,右键单击名为“实验室 01 - 报价”的默认查询名称以将查询重命名为“报价”。
练习 4:使用 Power Query 进行逆透视 - 报价
在此练习中,你将使用 Power Query 转换来构造 Power Pivot 的报价数据。
任务 1:使用第一行作为标题转换按钮
在此任务中,将具有列标题值的第一行移动到表格标题。
在“主页”菜单中,选择“将第一行作为标题”按钮。
任务 2:使用逆透视转换菜单选项
在此任务中,你将对报价数据进行逆透视。
在“预览”窗格中,右键单击“CustID”列以显示菜单选项。
然后选择“逆透视其他列”选项。
双击“Attribute”列,然后将其重命名为“QuoteDate”。
双击“Value”列,然后将其重命名为“QuoteAmt”。
之前
之后
练习 5:使用 Power Query 进行清理 - 报价
在此练习中,你将使用 Power Query 转换来清理报价数据。
任务 1:使用替换转换
在此任务中,你将使用替换方法将 QuoteDate 更改为可转换为“日期”数据类型的完整日期。
在“预览”窗格中,右键单击“QuoteDate”列以显示菜单选项。
然后,选择“替换值...”选项。
在“替换值”UI 窗口中,执行以下操作:
在“要查找的值”文本框中输入一个连字符 -。
在“替换为”文本框中输入“/1/”。
- 选择“确定”按钮。
任务 2:使用数据类型图标
在此任务中,将使用数据类型图标将数据类型从“文本”更改为“日期”。
选择 QuoteDate 列标题中的 ABC 图标,该图标指示该列是文本数据类型。
从数据类型菜单选项中选择“日期”选项。
任务 3:关闭并加载到数据模型
在此任务中,将“客户”和“报价”表加载到数据模型。
在“主页”菜单中,选择“关闭并加载”>“关闭并加载到...”。
在“导入”窗口中,选择“仅创建连接”单选按钮。
选中“将此数据添加到数据模型”旁边的框。
选择“确定” 按钮。
注意
加载的表显示在“查询和连接”窗格窗口中,其中加载了总行数。
注意
此时,我们已使用 Power Query 连接到数据源,并选中了“将此数据添加到数据模型”复选框选项。 但是,我们实际上并未看到此数据已加载到何处。 在实验室 02A 中,使用 Power BI Desktop 导入 Power Query 连接、客户表和报价表来创建数据模型。
注意
要编辑查询并重新启动 Power Query 编辑器窗口,请选择“数据”>“查询和连接”以显示“查询和连接”窗格。
注意
当你选择“关闭并加载到…”且首次设置“导入数据”选项时,这是默认设置。 你始终可以编辑这些设置,方法是右键单击“查询和连接”窗格中的“查询”,然后选择“加载到...”选项以显示并更新“导入数据”设置。
任务 4:保存文件
在此任务中,你将保存包含“客户”和“报价”查询连接的 Excel 文件。
在 Excel 主功能区中,选择“文件”>“保存”。
转到 C:\ANALYST-LABS\Lab 01 文件夹,然后将文件保存为 Lab 01 - My Solution.xlsx。
摘要
在本实验室中,你在 Excel 中使用 Power Query 连接到了 CSV 和 XLSX 源数据文件,使用“示例中的列”创建了新列,在 Power Query 中逆透视并应用了转换,将源数据加载到了数据模型,然后保存了包含数据连接的 Excel 文件。