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+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

Need Help
  • selimovd's avatar
    selimovd
    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

6 Replies

  • selimovd's avatar
    selimovd
    Most Valuable Professional
    5 years ago

    Hey Fcoatis ,

     

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

     

    Thank you and best regards

    Denis

  • Fcoatis's avatar
    Fcoatis
    Post Patron
    5 years ago

    Hi selimovd 

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

    • 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"