CODEWITHSUNDEEP
powerquerym
Fcoatis
Fcoatis

Reputation: 115

Expression.Error: We cannot convert the value 3 to type Function

need some help in this code. Somehow I´m getting the syntax wrong:

    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"

error image

Here´s is what I am expecting to achieve.

Example

I Just figured it out! Thanks for those how tried to help!

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"

Upvotes: 0

Views: 1647

Answers (2)

Aleksei Zhigulin
Aleksei Zhigulin

Reputation: 1634

If you need to get second to last row of the table (as table), the last step should be like this:

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

If your goal is to get a table, which contains second to last row for each Asset, then you may use following code:

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

enter image description here

Upvotes: 1

horseyride
horseyride

Reputation: 21373

Are you trying to get the t_asset table from the previous row?

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 Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 0, 1),
#"Added Custom" = Table.AddColumn(#"Added Index", "Row-1", each try #"Added Index"{[Index]-1}[t_Asset] otherwise null)
in #"Added Custom"

Upvotes: 0

Related Questions