Sample data:
I have a table where 1 Order Number has multiple record in that. I want to retrieve just the most recent one.
1. Group by Order Number. Operation : All Rows
I need the last recent record by Order Date for each Order Number.
This is my last step in Power Query, now I have to delele everything from each until number]
I have end up with a code like this, and I am going to use a table function Table.Max () needs a table name and its atttribut as parameters (in this case).
each Table.Max(_ , "Order Date") ...for each row in my Table.Group() or groupped table retrieve the maximal value for "Order Date" attribute.
Expand the Subtable and pick records you want to return.
Final Table:
Or you can convert your records into a list by adding the attribute name on the end of the query. You will get a list and you can covert this list into a table. Or you can retrive just on specific record from the entire table by specifying its value using {3-1} ...this means I want to retrieve 3rd record only from order number 200.
I have Order number 202, 3rd record. On the left - top corner there is an option to convert this into a table.
To convert this table into a tabular format use Pivot transformation
And I have retrived one specific row from the table.
[attribe] ...if you want to access the attributes
{1-1} ...if you need to access the records. This is for the first record. You can also use {0}
Let's say I need to extract the highlithed text for any reason
Click on the Fx to add a custom step
Specify column name and row number
you can convert the number to table or list
And I have retrieved just one specific value from the entire table and can use it in ...reporting.
