How to extract last row
Today, we want to extract the last row with given currencies. Our data is sorted randomly. If we want to consider the date column, we will have to sort it first. We can do it manually, by going to the end of date and find the last row. However, we want to sort it in Power Query, which is more automatic. We just have to select one cell from our table, go to Data tab, and click on the From Table/Range command (Fig. 1).

This command exports our data to Power Query. We can see, that Power Query considers our data as a date and time. In our example, it isn’t important so let’s leave it like that. If we want to sort our dates, we just click on the A to Z sorting command on the Home tab (Fig. 2).

Now, the last row is the newest date (Fig. 3).

Since we are interested in currencies, we want to group our data by the Currency column. We have to click one cell from the Currency columns and go to the Group By command in the Home tab. In the Group By window that has appeared, we can see that the Currency bar is selected. In the Operation bar, we need to select All Rows and, in the New column name, let’s write temporary. Then we are pressing the OK button (Fig. 4).

And we have our data grouped by currencies (Fig. 5).

There is a table for each currency that contains all rows with a given currency. We have sorted the dates earlier, so in the tables, the last date is also the newest exchange rate. And the newest exchange rate is what we want (Fig. 6).

Let’s go to the Add Column tab, then to the Custom Column command. A window will appear, where we write the name. Let’s call it Last. Then, we need to write a formula in the Custom column formula box. We are writing Table.LastN command to attract the last row of the table. The function we are writing needs a table, so we are choosing our table called temporary, then we need to write the number of rows from the end. We want just one row, so let’s write it, then close the formula. This is the whole formula we need, so let’s press the OK button (Fig. 7).
=Table.LastN(temporary),1)

We can see that we have the Last column. In the column, there are tables, but each table contains only one row for each currency (Fig. 8).

We don’t need the temporary column, so let’s delete it (Fig. 9).

Now, we can expand the Last column by pressing the icon shown in Fig. 10. In the window that has appeared, we deselect the Currency and the Use original column name as prefix checkboxes. Then, we press the OK button (Fig. 10).

Now, we can see a proper table with currency, and the newest date with the exchange rate. Let’s go to the Home tab, and Close&Load to command (Fig. 11).

We moved back to Excel, where we have to select the Existing worksheet radio button, then click on cell F1 and deselect the Add this data to the Data Model checkbox, and press the OK button (Fig. 12).

And we have our results. However, we have to remember that Power Query not always extracts proper data formatting. That’s why we have to go to the Home tab and select proper data formatting (Fig. 13).

Let’s make a test and change some some dates in different currencies, then refresh our table (Fig. 14).

We can see that the Query is automatic when we refresh it (Fig. 15).
