Table.TransformRows()

Table.TransformRows()可以抽象地概括为function(table as table, transform as function) as list,大意为:该函数的第一个参数为表格,第一个参数按行分解所得的记录将会作为自变量传入第二个参数指定的函数,指定函数的结果会按照行的顺序化为串列的元素。这个函数等价于List.Transform(Table.ToRecords(table), each transform(_))。比如,=Table.TransformRows(#table({"A"}, {{1},{2}}),(x)=> x[A])的结果为{1,2}。这个函数与Record类的函数进行搭配可以获得意想不到的效果。

接下来将会使用M套路108式中的连续衰减问题来说明Table.TransformRows()的用法,如果对该问题的背景不太清楚,请自行回顾该问题的文章。

从上表(DB1)到下表的转换,如果需要比较严谨的解答,可以使用以下代码:

let
    Solution1=
        Table.FromRows(
            List.Transform(
                Table.ToRows( DB1 ),
                ( x ) ⇒
                    List.Accumulate(
                        List.Skip( x, 2 ),
                        List.FirstN( x, 2 ),
                        ( y, z ) ⇒
                            List.Combine(
                                {
                                    y,
                                    { List.Last( y ) - z }
                                }
                            )
                    )
            ),
            Table.ColumnNames( DB1 )
        ),

    DataType=
        Table.TransformColumnTypes(
            Solution1,
            List.Zip(
                {
                    Table.ColumnNames( DB1 ),
                    List.Combine(
                        {
                            { Text.Type },
                            List.Repeat( { Int64.Type }, 6 )
                        }
                    )
                }
            )
        )
in
    DataType

如果很有信心制作的报表的格式不会发生变化,可以通过Table.FromRecords()+Table.TransformRows()+Record.TransformFields()的组合进行解答;

let
    Solution2=
        Table.FromRecords(
            Table.TransformRows(
                DB1,
                ( x ) ⇒
                    Record.TransformFields(
                        x,
                        {
                            {
                                "W1",
                                each x[期间库存] - x[W1]
                            },
                            {
                                "W2",
                                each x[期间库存] - x[W1] - x[W2]
                            },
                            {
                                "W3",
                                each x[期间库存] - x[W1] - x[W2] - x[W3]
                            },
                            {
                                "W4",
                                each x[期间库存] - x[W1] - x[W2] - x[W3] - x[W4]
                            },
                            {
                                "W5",
                                each x[期间库存] - x[W1] - x[W2] - x[W3] - x[W4] - x[W5]
                            }
                        }
                    )
            ),
            type table [ 科号 = text, 期间库存 = Int64.Type, W1 = Int64.Type, W2 = Int64.Type, W3 = Int64.Type, W4 = Int64.Type, W5 = Int64.Type ]
        )
in
    Solution2

DB1表格按行分解所得的x将会传入Record.TransformFields(),字段W1到字段W5会被转化为期间库存减去累积的使用量。

前文提及Table.TransformRows()等价于List.Transform(Table.ToRecords(table), each transform(_)),以下代码将会说明这点:

let
    Solution2=
        Table.FromRecords(
            List.Transform(
                Table.ToRecords( DB1 ),
                ( x ) ⇒
                    Record.TransformFields(
                        x,
                        {
                            {
                                "W1",
                                each x[期间库存] - x[W1]
                            },
                            {
                                "W2",
                                each x[期间库存] - x[W1] - x[W2]
                            },
                            {
                                "W3",
                                each x[期间库存] - x[W1] - x[W2] - x[W3]
                            },
                            {
                                "W4",
                                each x[期间库存] - x[W1] - x[W2] - x[W3] - x[W4]
                            },
                            {
                                "W5",
                                each x[期间库存] - x[W1] - x[W2] - x[W3] - x[W4] - x[W5]
                            }
                        }
                    )
            ),
            type table [ 科号 = text, 期间库存 = Int64.Type, W1 = Int64.Type, W2 = Int64.Type, W3 = Int64.Type, W4 = Int64.Type, W5 = Int64.Type ]
        )
in
    Solution2

Table.TransformRows()配合Record.AddField()还可以模拟Table.AddColumn(),比如说,如果想为DB1添加计算五周的平均库存消耗的列,可以通过以下代码实现:

let
    AddCol=
        Table.FromRecords(
            Table.TransformRows(
                DB1,
                (x) ⇒
                    Record.AddField(
                        x,
                        "Avg",
                        List.Average(
                            Record.FieldValues(
                                Record.RemoveFields(
                                    x,
                                    {"科号","期间库存"}
                                )
                            )
                        )
                    )
            ),
        type table [ 科号 = text, 期间库存 = Int64.Type, W1 = Int64.Type, W2 = Int64.Type, W3 = Int64.Type, W4 = Int64.Type, W5 = Int64.Type, Avg = number ] 
        )
in
    AddCol

Table.TransformRows()与Record.RemoveFields()组合可以模拟Table.RemoveColumns()。比方说,如果想移除DB1的科号列,可以使用以下代码:

let
    RemoveCol=
        Table.FromRecords(
            Table.TransformRows(
                DB1,
                ( x ) ⇒
                    Record.RemoveFields(
                        x,
                        "科号"
                    )
            
            ),
            type table [ 期间库存 = Int64.Type, W1 = Int64.Type, W2 = Int64.Type, W3 = Int64.Type, W4 = Int64.Type, W5 = Int64.Type ]
        )
in
    RemoveCol

Table.TransformColumns()对列的转换是不可以参考同一个表格的其他列,要突破这个限制,需要使用上文提及的Table.FromRecords()+Table.TransformRows()+Record.TransformFields()的组合。

附件

5 Replies to “Table.TransformRows()”

  1. Solution1=
    Table.FromRows(
    List.Transform(
    Table.ToRows( DB1 ),
    ( x ) ⇒
    List.Accumulate(
    List.Skip( x, 2 ),
    List.FirstN( x, 2 ),
    ( y, z ) ⇒
    List.Combine(
    {
    y,
    { List.Last( y ) - z }
    }
    )
    )
    ),

    老师好,有个问题希望能帮忙解答,参数y和z不是分别指代的是y=List.Skip( x, 2 )和z=List.FirstN( x, 2 )吗?,那照理来说List.Combine的参数不是应该是List.Combine(z,{List.Last(y)-z}),但似乎这个又不对了这是为什么?

    1. 根据List.Accumulate()的特性,在进入最初的循环时,y指代该函数的第二个参数, z指代该函数的第一个参数的第一个元素。

  2. 请教下:在”List.Transform(自定义1,(x)=>List.Accumulate(List.Skip(x,2),List.FirstN(x,2),(y,z)=> List.Combine({y,{List.Last(y)-z}})))”公式中,我看不懂,为什么“List.Combine({y,{List.Last(y)-z}})”形成成新的y?

    1. Table.ToRows()把表格以行为单位拆解成多个List,然后依次把这些List分解为(1)不含头两个元素的部分(List.Accumulate()的第一个参数)和(2)只含有头两个元素的部分(List.Accumulate()的第二个参数),所以y指的是(2)。

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注