powerquery - Expression.Error：我们无法将值 3 转换为函数类型

Question

在这段代码中需要一些帮助。不知何故，我的语法错误：

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdE7CsMwFETRrRjVBuk9fV1KK0hvXCYQCAhi758YnCoEdMuBU91ZV+OdFbHqzGxu/X08+uvZp1rrueu+349JzDYDpox5xgJjkbHEWGasMLZcTKwoyDtiyphnLDAWGUuMZcYKY1deidadL8gg75ApY56xwFhkLDGWGSuMffOmP6y19sN0QS8MmTLmGQuMRcYSY5mxwtiZd/sA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Portofolio = _t, Asset = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Asset", Order.Ascending},{"Date", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Asset"}, {{"t_Asset", each _, type table [Date=nullable text, Portofolio=nullable text, Asset=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Row-1", each Table.SelectRows([t_Asset], Table.RowCount([t_Asset])-1))
in
    #"Added Custom"

错误图像

这是我期望实现的。

例子

我刚刚想通了！感谢那些试图提供帮助的人！

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdE7CsMwFETRrRjVBuk9fV1KK0hvXCYQCAhi758YnCoEdMuBU91ZV+OdFbHqzGxu/X08+uvZp1rrueu+349JzDYDpox5xgJjkbHEWGasMLZcTKwoyDtiyphnLDAWGUuMZcYKY1deidadL8gg75ApY56xwFhkLDGWGSuMffOmP6y19sN0QS8MmTLmGQuMRcYSY5mxwtiZd/sA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Portofolio = _t, Asset = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
        #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Asset", Order.Ascending},{"Date", Order.Ascending}}),
        #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Asset"}, {{"t_Asset", each _, type table [Date=nullable text, Portofolio=nullable text, Asset=nullable text]}}),
        #"Added Custom" = Table.AddColumn(#"Grouped Rows", "t_Index", each Table.AddIndexColumn([t_Asset], "Index", 1, 1, Int64.Type)),
        #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"t_Asset"}),
        #"Added Custom1" = Table.AddColumn(#"Removed Columns", "Portfolio", each Table.SelectRows([t_Index],(Innertable)=> Innertable[Index] = Table.RowCount([t_Index])-1)),
        #"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"t_Index"}),
        #"Expanded Portfolio" = Table.ExpandTableColumn(#"Removed Columns1", "Portfolio", {"Portofolio"}, {"Portofolio"})
in
    #"Expanded Portfolio"

标签： powerquerym

Answer 1

如果您需要获取表格的倒数第二行（作为表格），最后一步应该是这样的：

= Table.AddColumn(#"Grouped Rows", "Row-1", each Table.LastN(Table.RemoveLastN([t_Asset]),1))

如果您的目标是获得一个表格，其中包含每个资产的倒数第二行，那么您可以使用以下代码：

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdE7CsMwFETRrRjVBuk9fV1KK0hvXCYQCAhi758YnCoEdMuBU91ZV+OdFbHqzGxu/X08+uvZp1rrueu+349JzDYDpox5xgJjkbHEWGasMLZcTKwoyDtiyphnLDAWGUuMZcYKY1deidadL8gg75ApY56xwFhkLDGWGSuMffOmP6y19sN0QS8MmTLmGQuMRcYSY5mxwtiZd/sA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Portofolio = _t, Asset = _t]),
    types = Table.TransformColumnTypes(Source,{"Date", type date}),
    group = Table.Group(types, {"Asset"}, {"temp", each let a = Table.Sort(_,{"Date",0})
                                                        in a{Table.RowCount(_)-2}}),
    expand = Table.ExpandRecordColumn(group, "temp", {"Date", "Portofolio"})
in
    expand