通过自联接进行Power Query M循环表/查找 [英] Power Query M loop table / lookup via a self-join

问题描述

首先，我是电源查询的新手，所以我正在迈出第一步.但是我需要尝试在工作中花些时间，以便获得一些喘息的时间来学习.

First of all I'm new to power query, so I'm taking the first steps. But I need to try to deliver sometime at work so I can gain some breathing time to learn.

我有下表(示例):

Orig_Item       Alt_Item    
5.7             5.10
79.19           79.60
79.60           79.86
10.10           

我需要创建一个列，该列将循环表并显示最终的Alt_Item.因此结果如下:

And I need to create a column that will loop the table and display the final Alt_Item. So the result would be the following:

Orig_Item       Alt_Item        Final_Item  
5.7             5.10            5.10    
79.19           79.60           79.86   
79.60           79.86           79.86   
10.10           

非常感谢

推荐答案

实际上，对于第一次Power Query体验来说，这太复杂了.

Actually, this is far too complicated for a first Power Query experience.

如果这是您必须要做的，那就照做，但是您应该意识到自己正在开始一项艰巨的任务.

If that's what you've got to do, then so be it, but you should be aware that you are starting with a quite difficult task.

小细节:我希望最后的Final_Item为10.10.根据该示例，如果Alt_Item为null，则Final_Item将为null.如果那是不正确的，那将是您相应地调整以下代码的不错的第一步.

Small detail: I would expect the last Final_Item to be 10.10. According to the example, the Final_Item will be null if Alt_Item is null. If that is not correct, well that would be a nice first step for you to adjust the code below accordingly.

您可以创建一个新的空白查询，将此代码复制并粘贴到高级编辑器"中(替换默认代码)，然后将源"调整为表名.

You can create a new blank query, copy and paste this code in the Advanced Editor (replacing the default code) and adjust the Source to your table name.

let
    Source = Table.Buffer(Table1),
    AddedFinal_Item = 
      Table.AddColumn(
        Source, 
        "Final_Item", 
        each if [Alt_Item] = null
             then null
             else List.Last(
                    List.Generate(
                        () => [Final_Item = [Alt_Item], Continue = true],
                        each [Continue],
                        each [Final_Item = 
                                    Table.First(
                                        Table.SelectRows(
                                            Source, 
                                            (x) => x[Orig_Item] = [Final_Item]),
                                        [Alt_Item = "not found"]
                                                        )[Alt_Item], 
                              Continue = Final_Item <> "not found"],
                        each [Final_Item])))
in
    AddedFinal_Item

此代码使用功能 List.Generate 执行循环. 出于性能原因，在调用List.Generate之前，应始终将表缓冲在内存(Table.Buffer)中.

This code uses function List.Generate to perform the looping. For performance reasons, the table should always be buffered in memory (Table.Buffer), before invoking List.Generate.

List.Generate是最复杂的Power Query函数之一.

List.Generate is one of the most complex Power Query functions.

它需要4个参数，每个参数本身就是一个函数.

It requires 4 arguments, each of which is a function in itself.

在这种情况下，第一个参数以()开头，其他参数以(3)开头(从上面的概述中应该很清楚:它们是对齐的).

In this case the first argument starts with () and the other 3 with each (it should be clear from the outline above: they are aligned).

参数1 定义了初始值:具有Final_Item和Continue字段的记录.

Argument 1 defines the initial values: a record with fields Final_Item and Continue.

参数2 是继续执行的条件:如果找到了一个项目.

Argument 2 is the condition to continue: if an item is found.

参数3 是每次迭代中的实际转换:在Source表中(使用Table.SelectRows)搜索等于Alt_Item的Orig_Item.这包装在Table.First中，它返回第一个记录(如果找到)，并在没有找到任何内容的情况下接受默认值，在这种情况下，该记录的字段为Alt_Item，值为未找到"，从该结果中得出记录字段的值返回[Alt_Item]，它是第一条记录的值，或者是默认值的未找到".

Argument 3 is the actual transformation in each iteration: the Source table is searched (with Table.SelectRows) for an Orig_Item equal to Alt_Item. This is wrapped in Table.First, which returns the first record (if any found) and accepts a default value if nothing found, in this case a record with field Alt_Item with value "not found", From this result the value of record field [Alt_Item] is returned, which is either the value of the first record, or "not found" from the default value.

如果该值是未找到"，则继续"变为 false ，并且迭代将停止.

If the value is "not found", then Continue becomes false and the iterations will stop.

参数4 是将返回的值:Final_Item.

Argument 4 is the value that will be returned: Final_Item.

List.Generate返回每次迭代的所有值的列表.仅需要最后一个值，因此List.Generate被包装在List.Last中.

List.Generate returns a list of all values from each iteration. Only the last value is required, so List.Generate is wrapped in List.Last.

最后一句话:在Power Query中很少需要实际的循环，我认为应尽可能避免.但是，在这种情况下，这是一种可行的解决方案，因为您事先不知道会遇到多少个Alt_Item. List.Generate的替代方法是使用递归函数. 另外， List.Accumulate 接近循环，但是具有固定的迭代次数.

Final remark: actual looping is rarely required in Power Query and I think it should be avoided as much as possible. In this case, however, it is a feasible solution as you don't know in advance how many Alt_Items will be encountered. An alternative for List.Generate is using a resursive function. Also List.Accumulate is close to looping, but that has a fixed number of iterations.

这篇关于通过自联接进行Power Query M循环表/查找的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！