When you load the sales data into Power Query and apply some transformations, Power Query creates a new worksheet, but the original sales data worksheet stays the same. This approach makes it easy to experiment with the data without changing the data source.
Notice that there are product categories and sales data worksheets. Open the Sales Data worksheet.
Position the cursor on the Excel table, and select Data > Get & Transform Data > From Table/Range.
Excel opens the Power Query Editor with your data displayed in a preview pane.
To display all query tables in the workbook from the Queries pane, select the arrow to the left of the preview pane.
The Product Sales table is listed last, then the World Cup Results table, and then the tables used earlier in the tutorial workbook. As you add new tables, they appear here automatically.
The Query Settings pane shows the transformation steps you take. You can preview, change, and delete any steps. Power Query adds a few steps for you. The following table summarizes important features:
Feature Description Source Defines the source for your data. Changed Type Power Query interprets the data types. You can adjust these later. Home Tab Use to preview your data and make transformations. Close & Load When finished, select Close & Load to load the query results into Excel. Transform Tab Provides advanced transformation options. Add Column Calculates data from existing columns, such as the day of the week from a date or custom calculations. View Tab Provides additional options, such as opening the Advanced Query Editor.
To return the transformed data to Excel, select Home > Close & Load.
Note
Whether or not you transform the data, Power Query loads the query results to a new worksheet by default. You can choose other load destinations by using Load To options. It's a good idea to rename the new worksheet to clearly distinguish it from the original worksheet. Any additional transformations you make to the query are reflected in the loaded results.