Forum Discussion
Expression.Error: We cannot convert the value 3 to type Function.
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
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
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"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