Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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"
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"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
100 | |
51 | |
19 | |
12 | |
11 |