以为你了解 Excel 吗？通过 Power Query 将你的分析技能提升到新高度！

原文towardsdatascience.com/think-you-know-excel-take-your-analytics-skills-to-the-next-level-with-power-query-930e2267006e?sourcecollection_archive---------3-----------------------#2024-11-295 个实用的案例证明 Power Query 值得深入探索。https://medium.com/ihetsevi?sourcepost_page---byline--930e2267006e--------------------------------https://towardsdatascience.com/?sourcepost_page---byline--930e2267006e-------------------------------- Ilona Hetsevich·发表于Towards Data Science ·阅读时长7 分钟·2024 年 11 月 29 日–我有一个 confession我一直生活在石头下 。不是字面上的意思但我怎么能解释直到现在我才发现Power Query在 Excel 中存在呢想象一下你意识到那些花费了无数小时整理 VLOOKUP、嵌套 IF 和处理凌乱数据的时间其实可以通过几个简单的点击来替代。https://github.com/OpenDocCN/towardsdatascience-blog-zh-2024/raw/master/docs/img/67bcf0948fe76f1fa809ce4267377f53.png图片由作者生成Power Query 能够做 Excel 公式可以做的一切——只是更快、更智能且更少令人沮丧。从合并数据集到轻松的转换与创建计算列可能性是无穷无尽的。网络上已经有大量文章和视频提供了详细的步骤指南帮助你入门所以我不会再写一篇如何操作的教程。相反我将分享一些让我大吃一惊的功能并通过5 个使用案例希望能够激励你亲自探索这个强大的工具。为了展示其卓越的功能我将使用一个简单的电子商务数据集该数据集包含两个 CSV 文件一个是客户数据另一个是交易数据。https://github.com/OpenDocCN/towardsdatascience-blog-zh-2024/raw/master/docs/img/5632ab1efd9773d7598f666109dfb2e5.pnghttps://github.com/OpenDocCN/towardsdatascience-blog-zh-2024/raw/master/docs/img/306c4573351da08a946e33b9c7da0630.png这些屏幕展示了每个 CSV 文件的前 10 行样本由作者生成使用案例 1合并数据集VLOOKUP 的更智能替代方案当涉及到数据合并时我们都会使用 VLOOKUP。但让我们面对现实——VLOOKUP 有它的局限性。为什么 Power Query 比 VLOOKUP 更好✨你可以一步完成从多个列连接数据无需重复查找。你的查找列不必在左边——无论它位于何处都可以正常工作。它支持不同的连接类型左连接、右连接、全连接、内连接、反连接让你对数据如何合并有更多的控制。与 VLOOKUP 不同Power Query 不仅限于通过一个列进行连接。你甚至可以使用模糊匹配来处理带有拼写错误的混乱数据集如何在 Power Query 中合并数据集 在首页选项卡中选择**“合并查询”**。选择你要合并的两个数据集或文件。选择用于连接的列并指定连接类型例如左连接保留一个表中的所有行或者内连接仅匹配重叠的行。合并后选择你想包含在最终数据集中的列。将转换后的数据加载回 Excel。几秒钟内完成⏱️https://github.com/OpenDocCN/towardsdatascience-blog-zh-2024/raw/master/docs/img/86919782253f21446a4c8447685333e2.png这个 gif 解释了使用 Power Query 合并两个文件的过程由作者生成用例 2列转换Power Query 让你非常轻松地转换数据。从快速计算到日期处理和创建区间它能在几秒钟内完成在 Excel 中可能需要几分钟甚至更长时间的工作。让我通过几个示例来展示给你看。执行快速计算 ➕➖➗✖️假设你需要将价格从美元转换为欧元并计算销售的总值。在 Power Query 中你可以转到**“转换”选项卡在“标准”下选择“乘法”**。将所有价格乘以 0.95 以转换货币。添加一个新列通过将价格乘以数量来计算总销售额。为此转到**“添加列”选项卡并选择“自定义列”**。所有这些操作只需几次点击即可完成。https://github.com/OpenDocCN/towardsdatascience-blog-zh-2024/raw/master/docs/img/35187c82943a862422ab4f186736db34.png这个 gif 解释了使用 Power Query 进行快速计算的过程由作者生成轻松修改日期 Power Query 使处理日期变得简单。你可以快速提取月份名称或者仅显示前三个字母例如JanFebMar以获得更简洁的外观使用的是内置功能。选择日期格式的列。转到**“添加列”选项卡在“日期”下选择“月份”然后选择“月份名称”**。选择包含月份名称的新列。在**“提取”下选择“提取前几个字符”**然后在弹出窗口中指定要提取的字符数此处为 3。无需复杂的公式https://github.com/OpenDocCN/towardsdatascience-blog-zh-2024/raw/master/docs/img/0d52971317e46b1bb370d0b787915b0b.png这个 gif 解释了使用 Power Query 修改日期的过程由作者生成创建无复杂公式的区间 我不知道你怎么样但我总是忘记在 Excel 中创建区间的 IF 公式语法如果有多个区间的话公式会变得非常长。不过使用 Power Query就简单多了。如果你使用的是 Windows你可以使用“示例列”功能。你只需要输入一个区间范围的示例例如“41–50”Power Query 会自动为你填充其余的值节省你的时间。对于我们使用 Mac 的人像我一样遗憾的是这个功能不可用。但别担心还有解决办法你仍然可以使用“条件列”功能通过设置逻辑规则来将数据分类到不同的区间。https://github.com/OpenDocCN/towardsdatascience-blog-zh-2024/raw/master/docs/img/c936a197620df72f9efc1de14833cf89.png这个 GIF 解释了如何使用“条件列”功能创建区间的过程由作者生成用例 3填充缺失数据 我们都处理过包含缺失值的数据集——无论是因为输入不完整还是数据不一致。在大多数情况下你不想留下这些空白而是希望填补这些空缺。这时 Power Query 就显得特别有用。假设我们在“每单位价格”列中有缺失值并且我们想要用该类别的平均价格来替换这些缺失值。以下是如何通过几个简单步骤完成的筛选“产品类别”列只显示“美容”条目。在选中“每单位价格”列后在屏幕底部的统计部分查看美容类别的平均价格。移除“产品类别”列上的筛选器重新显示所有类别。再次选择“每单位价格”列转到“转换”选项卡点击“替换值”。在对话框中输入“null”作为要替换的值并在“替换为”字段中输入美容类别的平均价格。就这样你已经用平均值填充了缺失的数据——只需几个点击。https://github.com/OpenDocCN/towardsdatascience-blog-zh-2024/raw/master/docs/img/6c7a57e64d045bcb41ba886e413f5f3f.png这个 GIF 解释了如何使用 Power Query 填充缺失值的过程由作者生成用例 4转变数据以进行更好的分析 ️Power Query 非常适合将数据转换成符合分析需求的格式。例如如果你想按月汇总总销售额并查看趋势变化可以使用“按组分组”和“转置”功能。下面是只需 4 步即可完成的方法选择“月份”列按月对数据进行分组。在“转换”选项卡中点击“按组分组”。为“总价值欧元”添加新的聚合并选择求和以计算每个月的总销售额。最后点击“转置”将行和列互换将月份转为列标题。完成后将数据加载回 Excel并创建折线图以可视化销售趋势https://github.com/OpenDocCN/towardsdatascience-blog-zh-2024/raw/master/docs/img/5a3703ddd40df04f9e93494b817070bb.png这个 GIF 解释了如何使用“按组分组”和“转置”功能的过程由作者生成用例 5使用 M 公式语言的自定义列 M 公式语言让你超越典型的 Power Query 转换允许进行更复杂的计算和逻辑处理。当你需要为数据创建自定义解决方案时它非常适用。例如假设你的销售数据中的月份没有正确排序。你可以使用M 公式为每个月分配一个数值然后按照正确的顺序排序而不需要手动调整。转到**“转换”并选择“自定义列”**选项。在弹出窗口中使用“if-else if”逻辑输入以为每个月分配一个数值。按照新列的升序ASC进行排序。排序完成后你甚至可以删除“月份顺序”列。完成后你的月份就会按正确的顺序排列。if[Month short]Janthen1elseif[Month short]Febthen2elseif[Month short]Marthen3elseif[Month short]Aprthen4elseif[Month short]Maythen5elseif[Month short]Junthen6elseif[Month short]Julthen7elseif[Month short]Augthen8elseif[Month short]Septhen9elseif[Month short]Octthen10elseif[Month short]Novthen11elseif[Month short]Decthen12elsenullhttps://github.com/OpenDocCN/towardsdatascience-blog-zh-2024/raw/master/docs/img/e2343d3408394a76ef13f442c167cba8.png这个 gif 解释了在 Power Query 中使用 M 公式语言的过程由作者生成最后但同样重要的是……Power Query 会跟踪应用步骤日志中的每个更改 因此如果你想返回修改或撤销任何操作超级简单。感到启发了吗‍♂️我希望你现在对尝试这些功能的兴奋感和我一样如果我是你我会立刻跳进 Power Query。