Forum Discussion

SiGill1979's avatar
SiGill1979
Frequent Visitor
6 years ago
Solved

Last item in a column

How can I get the last entry in a column.

I have a table of data and I need the last item in that table. Example below:

I need to turn the table on the left into the table on the right using Power Query.

 

Baring in mind there will be more dataas the years go on.

 

Any Help would be appreciated.

 

Many Thanks

 

Simon

 

Need Help
  • Mariusz's avatar
    Mariusz
    6 years ago

    Hi SiGill1979 

     

    Try the below.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hctBCsAwCETRu2QdSjRq9Cwh979GS7DQgsTNMPD4cxZsYKUW0mfgQh24D8iQsmrgsA+Jaugs3itEbuQ+OOytdXf89+YOb4/8dW6JQ+J49iRP6n5minjd", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Year = _t, #"Wk No" = _t, USD = _t, EUR = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Wk No", Int64.Type}, {"USD", type number}, {"EUR", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Year", "Wk No"}, {{"tbl", each Table.LastN( _ , 1), type table [Year=number, Wk No=number, USD=number, EUR=number]}} ),
    #"Expanded tbl" = Table.ExpandTableColumn(#"Grouped Rows", "tbl", {"USD", "EUR"}, {"USD", "EUR"})
in
    #"Expanded tbl"

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

     

1 Reply

  • Mariusz's avatar
    Mariusz
    Community Champion
    6 years ago

    Hi SiGill1979 

     

    Try the below.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hctBCsAwCETRu2QdSjRq9Cwh979GS7DQgsTNMPD4cxZsYKUW0mfgQh24D8iQsmrgsA+Jaugs3itEbuQ+OOytdXf89+YOb4/8dW6JQ+J49iRP6n5minjd", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Year = _t, #"Wk No" = _t, USD = _t, EUR = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Wk No", Int64.Type}, {"USD", type number}, {"EUR", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Year", "Wk No"}, {{"tbl", each Table.LastN( _ , 1), type table [Year=number, Wk No=number, USD=number, EUR=number]}} ),
    #"Expanded tbl" = Table.ExpandTableColumn(#"Grouped Rows", "tbl", {"USD", "EUR"}, {"USD", "EUR"})
in
    #"Expanded tbl"

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.