Adding a timestamp column using Power Query
During my most recent Foundations of Microsoft Excel course, someone asked me how they could copy data from one worksheet to another each day and automatically add a new column containing a timestamp. Since this is a daily routine, you can achieve this with Power Query as follows:
Select the data and choose Data => Get Data => From Other Sources => From Table/Range. If this option isn't available, choose Data => Get Date => From File => From Excel Workbook, follow the wizard's steps to select the data, then transform it.
In the Power Query Editor, choose Add Column => Custom Column.
In the Custom Column dialog box, type a name for the new column and type DateTime.LocalNow() in the custom column formula box, then click OK.
Choose Home => Close & Load to load the data to Excel.
In Excel, format the column as a date/time.
The data (including the timestamp) will update each time you refresh the query.
You'll be able to find these tips and more in the Excel Cookbook Power Query chapter.