为了进行【合并】，最好有一个列，在一个表中包含唯一的值，在另一个表中可以有重复的记录，这被称为一对多关系结构，该结构是确保最终得到的结果与所期望的一致的最好方法。...【注意】 Power Query 还支持一对一和多对多的连接。 在本例中，“SKU” 列在 “Inventory” 表中包含唯一值，而在 “Sales” 表中有重复记录，使用这一列连接两边。...这里保留是为了演示这些列不包含值，因为在 “COA” 表中没有找到匹配的记录。 10.2.2 右外部连接 该功能在 Power Query 叫做：【右外部 (第二个中的所有行，第一个中的匹配行)】。...为了避免意外产生的笛卡尔积，最好使用列分析工具来检查 “非重复值” 和 “唯一值” 的统计数据是否匹配如果 “非重复值” 和 “唯一值” 两个统计数据匹配，像本案例中 “SKU” 列一样（都是 “12”...虽然基本模糊匹配可能会导致匹配中出现误报（毕竟匹配到 80% 的相似性），但 Power Query 团队提供了一个默认值，该值限制了误报的数量，同时仍提供了模糊匹配功能。
经过分别对以上4中方法单独执行多列同时填充（Power Query数据合并法单独执行数据刷新）并计算时间，结果如下表所示： 从运行用时来看： VLookup函数和Index+Match函数的效率基本一样...； Lookup函数在大批量数据的查找中效率最低，甚至不能忍受； Power Query的效率非常高。...那么，如果我们在公式中也可以做到只匹配一次，后面所需要取的数据都跟着这次匹配的结果而直接得到，那么，效率是否会大有改善呢？...（Match公式列），用时约15秒； 同时根据已匹配的位置列填充G:L列（Index公式全部列），用时约1秒（双击填充柄直接出现进度条，不出现“正在计算，##%”过程）； 位置列和其他数据列同时填充...当然，使用公式的方法，即使在一定程度上进行改进，和Power Query相比仍然有很大的差距。因此，在数据量较大，数据处理较为复杂的情况下，建议使用Power Query来进行。
一、简介 相比Power BI，Power Query和Power Pivot在行列层级运行计算，Excel一直以来主要还是在单元格层面上的。...近年Excel提供了动态数组运算能力和一系列相关函数，能够类似于Power BI那样，直接在行列层级运算。一方面节省了公式填充复制的工作量，另一方面为更复杂的计算提供了可能性和便捷性。...FILTER - 根据您定义的标准过滤数据。 SORT - 按指定列对一系列单元格进行排序。 SORTBY - 按另一个范围或数组对一系列单元格进行排序。 RANDARRAY - 生成随机数数组。...WRAPCOLS - 根据每行指定的值数将行或列转换为二维数组。 WRAPROWS - 根据每列指定的值数将行或列重新整形为二维数组。 TAKE - 从数组的开头或结尾提取指定数量的连续行或列。...无法删除结果数列中的任意值 动态数组生成的结果，是一个整体，无法像平常excel列那样，删除其中任意的值。 3. 不支持超级表和Power Query 预告：下期将会逐步介绍动态数组函数的应用
在 Power BI 中没有【查询 & 连接】窗格，建议用户学习一种能在多个程序中都适用的方法来做到这一点。...图 8-13 Power Query 如何知道 “TranDate” 列值应该进入 “Date” 列呢 当【追加】两个表时，Power Query 将从第一个查询中加载数据。...如果任何标题不存在于现有列中，新的列将被添加。然后，它将适当的记录填入每个数据集的每一列，用 “null” 值填补所有空白。...按这个逻辑，这意味着 “TranDate” 列（出现在三月的查询中）在一月和二月中被填充为 “null” 值，因为 “Jan 2008” 的查询没有 “TranDate” 列。...“Date” 列为每个三月记录填充了 “null” 值，而 “TranDate” 列则保存了本应在 “Date” 列中出现的值。 解决这个问题的方法如下所示。
默认情况下，Power Query 在设置数据类型时只预览前 1000 行，这意味着如果数据集中的第一个小数值显示在第 1001 行，Power Query 将选择一个【整数】数据类型，在导入时对该列的所有行进行四舍五入...在这种情况下，Power Query 试图在 “Item Name” 列上设置数据类型，但是该列已经不存在了，因为它在上一步中已经被重命名为不同的名称了。...3.5 值错误 虽然步骤级错误绝对是 Power Query 中最严重的错误，但它们不是用户将在 Power Query 中遇到的唯一错误。另一个常见的错误类型是值错误。...当应用 “Changed Type” 操作时，Power Query 会尝试获取单元格中提供的值，并根据本机用户的【Windows 区域设置】中为该数据类型定义的格式将其转换为【整数】类型。...为了避免对本机电脑造成过重的负担，Power Query 限制了预览窗口中的数据量，并允许用户根据这些预览来建立自己的查询。
【注意】 在这个示例中不需要使用【使用区域设置】来更改数据类型。由于数据已经存在于 Excel 中，无论用户本机的【区域设置】是什么，Power Query 都能正确识别这些数据。...7.3 拆分列 拆分列，是另一种常用操作（特别是在从 “平面” 文件导入时），是根据某种分隔符或模式将数据点从单个列中拆分出来。...该信息只包括在列标题中，而不在数据内容中，如图 7-18 所示。...图 7-18 请记住，这里只是根据标题知道 “Don” 是一个烧烤（Grill）厨师 虽然拆分 “Cooks: Grill/Prep/Line” 列为多行会把员工放到行中，但事实是 “厨师” 的类型并不存在于记录中...它们的长度是一致的，而且还在筛选区显示可选择的值。但如果仔细观察，会发现搜索框上方的弹出菜单会根据列的数据类型来命名，并提供特定于该数据类型的筛选器。 如下所示。
如果不小心单击了【确定】，请关闭 Power Query 编辑器并丢弃该查询。本书的意图是让用户在这里获得长期的成功，所以在将它加载到 Power Query 之前，先把它格式化为表格格式。...此时，Power Query 再次做了一些关于数据的假设，并自动地应用了几个步骤来提升列标题和设置数据类型。此时，要做的就是调整数据类型并将数据加载到工作表中，按如下操作即可。...“Kind” 列显示数据列中的表包含的是哪种对象。 “Hidden” 告诉用户该对象是否可见。 需要注意的另一件事是，“Data” 列中显示的 “Table” 对象与其他预览数据的颜色不同。...另外，当连接到一个外部工作簿时，Power Query 总是先连接到该工作簿的路径，再导航到用户所选择的对象中，然后再连接到工作簿中。...在原 Excel 中，她并不包括在命名区域内，但作为从工作表中读取时，它就显示出来了。如果该列充满了 “null” 值，可以直接选择该列并将其删除，或者思考下，这里是不是可以直接将它删除呢？
5.1.2 程序如何解析平面数据 程序在解析数据时，需要知道如下三件事。 数据点是否由单个字符、一组字符或一致的宽度分隔。 一个完整的记录和另一个完整的记录是由什么字符或字符列分隔的。...【注意】 问问自己，是否曾经在 Excel 中打开一个 “CSV” 或 “文本” 文件，发现其中一半的日期是正确的，而另一半则显示为文本？...因此，它将该数据视为文本，并将其放置在一个单元格中。 该程序试图将 45.67 转换为一个值。当转换成功后，该值被放置在一个单元格中。（如果转换不成功，它将被视为文本）。...在 Excel 中，进入【获取数据】 【查询选项】当前工作簿的【区域设置】，在那里定义【区域设置】。所有新的连接都将使用该【区域设置】作为默认值来创建。...图 5-18 一个不同的值，但没有空值，是这样吗 在这一列的情况下，可以看到，虽然只有一个值，但它没有填充空单元格。
与 Excel 和 DAX 语言具有IFERROR函数的方式类似，Power Query具有自己的语法来测试和捕获错误。...如有关处理Power Query中的错误的文章中所述，错误可以出现在步骤或单元格级别。 本文将重点介绍如何根据自己的特定逻辑捕获和管理错误。...Excel 工作簿中的此表在标准速率列中出现 Excel 错误，例如 #NULL！、#REF！和 #DIV/0！。 将此表导入Power Query编辑器时，下图显示了表的外观。...将此新列命名 为“所有错误”。表达式 try 将值和错误转换为记录值，该值指示表达式是否 try 处理错误，以及正确的值或错误记录。...可以使用记录值展开此新创建的列，并通过选择列标题旁边的图标来查看要展开的可用字段。此操作将公开三个新字段：所有 Errors.HasError - 显示 标准速率 列中的值是否出错。
现在，如果决定重命名另一列，会发生什么？会再次得到一个新的步骤吗？一起来找出答案。就像在 Excel 中有多种方法处理同一个问题一样，在 Power Query 中也有多种方法处理同一个问题。...【注意】 当然，这个功能也它的有另一面。比方说，假设重命名了六个列，然后意识到不小心错误的重命名了某个列。虽然可以删除这个步骤，但这将删除整个步骤，包括正确的五个重命名操作。...虽然可以单击每一列左上方的图标来选择适当的数据类型，但这可能会花费相当多的时间，特别是当大量的列需要处理时。另一个技巧是让 Power Query 为所有列设置数据类型，然后覆盖想更改的数据类型。...【注意】 如果在 Power Query 中执行某项转换功能时，会启动一个界面来辅助配置，配置完毕后就会在该步骤旁出现一个齿轮图标，它将允许用户重新设置这个步骤。...考虑这样一个解决方案，构建了一个 Power Query 查询来执行一些重要的数据清洗，将结果放到 Excel 工作表的一个表中。然后，根据该数据表创建了一堆图表和报告。
备注若要查看Power Query中可用连接器的完整列表，请参阅Power Query中的连接器。提前筛选建议始终在查询的早期阶段或尽早筛选数据。...可以使用自动筛选菜单来显示列中找到的值的不同列表，以选择要保留或筛选掉的值。还可以使用搜索栏来帮助查找列中的值。还可以利用特定于类型的筛选器，例如日期、日期时间甚至日期时区列 的上 一个筛选器。...这些特定于类型的筛选器可帮助你创建动态筛选器，该筛选器将始终检索前 x 秒、分钟、小时、天、周、月、季度或年份中的数据，如下图所示。 备注若要详细了解如何基于列中的值筛选数据，请参阅 按值筛选。...在Power Query 编辑器中，此类操作只需要读取足够的源数据才能填充预览。如果可能，请先执行此类流式处理操作，最后执行任何成本更高的操作。...使用正确的数据类型Power Query中的一些功能与所选列的数据类型相关。 例如，选择日期列时，“添加列”菜单中的“日期和时间”列组下的可用选项将可用。 但如果列没有数据类型集，则这些选项将灰显。
另一个原因是，在实际的业务中，单个列中的许多值是相同的；例如，几千或几万种产品往往对应着数以百万计的销售交易记录。此时，列式数据库可以通过仅存储一次特定值并记录它所属的行来高度压缩数据。...若要实现这一点，模型必须保留指针列表，以跟踪列中的某个值到底位于哪一行中。当向表中添加更多列时，计算量显然会显著增加。因此，在 Power BI 模型中，“窄”的表比“宽”的表更高效。...在Power BI 模型中，通过 Power Query 加载数据时，会将所有数据类型统一转换为 Text。很显然，当您忘记在 Power Query 中显式进行类型转换时，数字列也会存储为文本。...当一些值只在外键列出现而不存在于主键列时，关系仍然可以存在。 如图2.5所示，模型会将每个未知的外键的值连接到一个空白行。模型中不会显示这个空白行，但是在报表中会显示。...图2.6 一个活动关系和两个非活动关系 在某个特定的计算中，可以使用 USERELATIONSHIP 函数来激活非活动关系，同时原有的活动关系在该计算中暂时失效。
使用 Power Query 的一个非常有趣的场景是，可以利用它从 Web 上抓取与业务相关的数据，并用它来丰富自己的公司数据。数据通常以两种不同的方式之一存储在 Web 上。 存储在网站中的文件。...短暂延迟后，Power Query 会根据用户的示例输入信息以及其他网页上的数据推断出用户真实的提取意图，并自动填充这一列的其他部分。...【警告】 如果用户的 “示例输入” 导致显示大量空值，则表示 Power Query 无法确定提取值的正确逻辑。 完成第一列后，双击列标题将其重命名，如果要添加更多列，请单击 “+” 图标。...似乎这不是问题的最糟糕部分，在导航过程结束时，表格的一列显示为原始文本，另一列包装在 元素中，这意味着需要进行额外的操作，如图 11-14 所示。...尽管该网站尽了很大努力来整理数据，但网站上的信息还远远不够完美，可能包含的数据并不完全真实。 另一个问题是数据更新的容易程度。
标签：Power Query 你碰到过要填充空行吗？就像下图1所示的这样。 图1 图1所示的工作表中，列A中有许多空行，要使用每段空行上方单元格中的内容来填充这些空行，结果应该如下图2所示。...在这点上，Power Query更聪明，它能够识别并使用相应的内容来填充空行。 1.单击功能区“数据”选项卡“获取和转换数据”组中的“获取数据——来自文件——从工作簿”。...2.在弹出的“导入数据”对话框中选择数据所在的工作簿，单击“导入”，在出现的“导航器”中选择该工作簿中相应的工作表。...3.在出现的“Power Query编辑器”中，选择类别“班级”所在的列，如下图3所示。...图3 4.在Power Query编辑器中，单击功能区“转换”选项卡“任意列”组中的“填充——向下”命令，如下图4所示。 图4 结果如下图5所示。 图5 正是我们想要的结果！
Power Query 可以在 Power BI 或 Excel 中使用，很多人一开始就在想到底用哪个平台来使用 Power Query，其实不必为此纠结，总有一天会意识到需要把查询复制到一个另一个中的...图 4-5 自定义 “WooCommerce” 连接器在 Excel 中不可用 （译者注：WooCommerce 是一个国际范围著名的电商程序，在国内应用较少，该连接器将允许 Power BI 可以直接从该电商程序中获取数据...图 4-17 为什么 “Date” 列所有的值都显示为 “Error” 在阅读错误信息时，可以看到该列正试图将 “43131” 设置为一个日期。但是这个数字是怎么来的呢？...数据将继续存在于 Excel 文件中，这意味着在 Excel 文件中进行的任何添加、删除或更新都只需简单的刷新即可。 来重做之前的例子，但这次选择创建一个与 Excel 文件的连接，而不是复制数据。...唯一的区别是，在本例中，数据仍然存在于 Excel 文件中，数据是从那里导入的，而不是复制它并把数据存储在 Power BI 文件中。所以现在，如果 Excel 文件移动了。
这个清单可以是单层的或多层的，因为每个“原”材料也会有它相应的下级物料，以及下下级物料…直到最终分解到最基本的元素，如阳光，空气，铁矿石等等…… 实际应用中，我们会根据需要从系统中下载适当层级数的物料表...这里分享一个用Power Query来解决的思路--选则使用Power Query主要是因为在实际工作中，我们要处理的物料清单会有成百上千，每个都是一个独立从系统下载的文件，而且每个清单里的数据量可能都很大...使用Power Query，一切就变得很容易了。 首先将表一读入Power Query。如果是很多张表，可以用文件夹方式读取并合并，用筛选去除不需要的行。 ?...用类似的方法，加入条件列“1级”，“2级”，“3级”。 ? 选中“0级-2级 ”3个新建的条件列，右键菜单选择填充，向下填充。...（注意：这里没有选到3级列是因为3级是最后一级，如果选中它再向下填充会误填了上一级的数） ? 把“0-3级”四个条件列都选中，删除其他列，以保持页面整洁。 ?
以这种方式使用列时，Power BI 模型会在后台自动创建一个隐式度量值（implicit measure）：隐式度量值是一个聚合函数，能够根据选择的方式对列中的值进行聚合运算。...例如，在 Excel 模型和数据仓库中，您可能会遇到一个指示器（indicator），该指示器确定某一行数据是否属于“当前年初至今”。同样，这是一个静态解决方案，不会让您得到两个月前的年初至今数据。...当然，还有一些其他方法，比如在 Power Query 中的通过输入一些参数（例如，表应跨越哪些年份）来创建日期表，不过本书并不打算对此展开讨论。...该公式提供了一个使用 FORMAT 函数的范例，该函数可用于应用基于某些值的各种格式，在本例中为日期值，同时还可以自定义设置区域格式。 以上公式的结果如图3.6所示。...但是实际上，其他人可能会基于您的模型来构建自己的报表。对于你们所有人来说，最好隐藏模型中会遮盖有用表、列和度量值的元素。 关系中的外键列应当隐藏：主键上相同的值，并且会正确地筛选关系的另一端。
在Excel、Power Query、SQL和Python里，都有同样叫法的功能。个人认为，中文“透视”过于意译，并没能很形象地传达出英文Pivot的本意：围绕枢轴旋转。...在Power Query 中，关键是选中锚定的维度（列），本例为公司列。然后选择【转换】——【逆透视列】——【逆透视其他列】即可。...初始数据加载后我们会发现，第一行和第一列有很多空值。而直接逆透视会把空值开头的行或列给删除过滤掉。 首先我们来解决纵向的维度。根据上文我们可知，逆透视要先锚定维度。...因此纵向的解决步骤有两步： 最左边的列向下填充，解决空值问题； 合并最左边两列，成为一列单一的维度。 经过这两步之后，我们成功将2*2维表转为2*1维表。...Power Query只有向上向下填充，没有横向填充，怎么处理空值？我们不妨把表倒转过来——转置，那么横向的问题就转化为纵向问题，就可以重复上述步骤解决了。
文章背景：在Excel中，如果想要实现近似匹配，可以使用Vlookup函数。在Power Query中，也可以实现查找并返回等于或介于两个数据点之间的值。...在Power Query中实现近似匹配的步骤如下： （1）连接到源表和查找表； （2）准备查找表。重命名键列，以确保它们在两个表中匹配。 （3）执行匹配。...追加查询； 筛选键列，升序排列； 筛选编号列，升序排列； 右击返回列，向下填充。 筛选编号列，取消勾选null值。...参考资料： [1] 精通Power Query [2] How to use VLOOKUP with approximate match in Excel
领取专属 10元无门槛券
手把手带您无忧上云
