Forum Discussion

Fcoatis's avatar
Fcoatis
Post Patron
5 years ago
Solved

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

Hi, Need some help here:   let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdE7CsMwFETRrRjVBuk9fV1KK0hvXCYQCAhi758YnCoEdMuBU91ZV+OdFbHqzGxu/X08+uvZp1rrueu+349JzDY...
Need Help
selimovd's avatar
selimovd
Most Valuable Professional
5 years ago

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's avatar
Fcoatis
Post Patron
5 years ago

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's avatar
    selimovd
    Most Valuable Professional
    5 years ago

    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