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

[Fcoatis]

Hi,

Need some help here:

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"

the last Added column tries to extract the last-1 row of the table t_Asset. But I´m getting this error. I know it can be done, just missing the right syntax.

Thanks in advance

[Fcoatis]

Hi @selimovd 

To be more specific I need to extract the last -1 record from the tables in column t_Asset as follow:

[selimovd]

Hey @Fcoatis ,

the following code should work. Probably there are faster ways, but I'm not a Guru in Power Query.

Check that out:

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.AddColumn(#"Grouped Rows", "WithIndex", each Table.AddIndexColumn([t_Asset], "Index", 1, 1)),
    #"Remove Last Row" = Table.AddColumn(#"Added Index", "LastRemoved", each Table.RemoveLastN([WithIndex],1)),
    #"Select Max Row" = Table.AddColumn(#"Remove Last Row", "Max-1", each Table.Max([LastRemoved], "Index")),
    #"Expanded Max-1" = Table.ExpandRecordColumn(#"Select Max Row", "Max-1", {"Date", "Portofolio", "Asset", "Index"}, {"Date", "Portofolio", "Asset.1", "Index"})
in
    #"Expanded Max-1"

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

Blog: WhatTheFact.bi

[Fcoatis]

Thank you @selimovd ,

I just figured it out:

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"

[selimovd · Accepted solution]

Hey @Fcoatis ,

I'm happy to hear that!

Just checked it, great approach with the inner table 👍 

Hope I helped you in the right direction with the index column 😉

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

Blog: WhatTheFact.bi

[Fcoatis]

Hi @selimovd I expect the last -1 record of table t_Asset.

[selimovd]

Hey @Fcoatis ,

what exactly do you expect as a result for column Row-1?

Thank you and best regards

Denis