How to extract last row

Today, we want to extract the last row with giv­en cur­ren­cies. Our data is sort­ed ran­dom­ly. If we want to con­sid­er the date col­umn, we will have to sort it first. We can do it man­u­al­ly, by going to the end of date and find the last row. How­ev­er, we want to sort it in Pow­er Query, which is more auto­mat­ic. We just have to select one cell from our table, go to Data tab, and click on the From Table/Range com­mand (Fig. 1). 

How to extract last row
 From Table/Range command
Fig. 1 From Table/Range command

This com­mand exports our data to Pow­er Query. We can see, that Pow­er Query con­sid­ers our data as a date and time. In our exam­ple, it isn’t impor­tant so let’s leave it like that. If we want to sort our dates, we just click on the A to Z sort­ing com­mand on the Home tab (Fig. 2). 

A to Z sorting command
Fig. 2 A to Z sort­ing command

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

The newest date in the last row
Fig. 3 The newest date in the last row

Since we are inter­est­ed in cur­ren­cies, we want to group our data by the Cur­ren­cy col­umn. We have to click one cell from the Cur­ren­cy columns and go to the Group By com­mand in the Home tab. In the Group By win­dow that has appeared, we can see that the Cur­ren­cy bar is select­ed. In the Oper­a­tion bar, we need to select All Rows and, in the New col­umn name, let’s write tem­po­rary. Then we are press­ing the OK but­ton (Fig. 4).

Group By window
Fig. 4 Group By window

And we have our data grouped by cur­ren­cies (Fig. 5). 

Data grouped by currencies
Fig. 5 Data grouped by currencies

There is a table for each cur­ren­cy that con­tains all rows with a giv­en cur­ren­cy. We have sort­ed the dates ear­li­er, so in the tables, the last date is also the newest exchange rate. And the newest exchange rate is what we want (Fig. 6). 

The newest exchange rate
Fig. 6 The newest exchange rate

Let’s go to the Add Col­umn tab, then to the Cus­tom Col­umn com­mand. A win­dow will appear, where we write the name. Let’s call it Last. Then, we need to write a for­mu­la in the Cus­tom col­umn for­mu­la box. We are writ­ing Table.LastN com­mand to attract the last row of the table. The func­tion we are writ­ing needs a table, so we are choos­ing our table called tem­po­rary, then we need to write the num­ber of rows from the end. We want just one row, so let’s write it, then close the for­mu­la. This is the whole for­mu­la we need, so let’s press the OK but­ton (Fig. 7). 

=Table.LastN(temporary),1)

The formula
Fig. 7 The formula

We can see that we have the Last col­umn. In the col­umn, there are tables, but each table con­tains only one row for each cur­ren­cy (Fig. 8). 

The Last column
Fig. 8 The Last column

We don’t need the tem­po­rary col­umn, so let’s delete it (Fig. 9).

Deleting the temporary column
Fig. 9 Delet­ing the tem­po­rary column

Now, we can expand the Last col­umn by press­ing the icon shown in Fig. 10. In the win­dow that has appeared, we des­e­lect the Cur­ren­cy and the Use orig­i­nal col­umn name as pre­fix check­box­es. Then, we press the OK but­ton (Fig. 10). 

Expanding the Last column
Fig. 10 Expand­ing the Last column

Now, we can see a prop­er table with cur­ren­cy, and the newest date with the exchange rate. Let’s go to the Home tab, and Close&Load to com­mand (Fig. 11). 

Close&Load to command
Fig. 11 Close&Load to command

We moved back to Excel, where we have to select the Exist­ing work­sheet radio but­ton, then click on cell F1 and des­e­lect the Add this data to the Data Mod­el check­box, and press the OK but­ton (Fig. 12). 

Import Data
Fig. 12 Import Data

And we have our results. How­ev­er, we have to remem­ber that Pow­er Query not always extracts prop­er data for­mat­ting. That’s why we have to go to the Home tab and select prop­er data for­mat­ting (Fig. 13). 

Results with proper dates
Fig. 13 Results with prop­er dates

Let’s make a test and change some some dates in dif­fer­ent cur­ren­cies, then refresh our table (Fig. 14). 

Refreshing the table
Fig. 14 Refresh­ing the table

We can see that the Query is auto­mat­ic when we refresh it (Fig. 15). 

Automatic refreshing in Query
Fig. 15 Auto­mat­ic refresh­ing in Query

https://www.youtube.com/watch?v=f0uORcZw73g