How to Open Nested Tables from Power Query in New Sheets

Microsoft Power Query is a powerful tool that allows you to easily import, clean, transform, and refresh your data. One of its features is the ability to import data from a variety of sources, including tables within Excel workbooks. However, if you have a table within a table (a nested table), you might find it difficult to work with them in Power Query. In this article, we will show you how to open nested tables from Power Query in new sheets, so you can work with them more easily.

What are nested tables?

Nested tables are tables that are contained within other tables. They can be useful for organizing data, but they can also be difficult to work with. In Excel, you can create nested tables by inserting a table within a table. In Power Query, you can create nested tables by merging queries that contain tables.

Why open nested tables in new sheets?

When you import a nested table into Power Query, it can be difficult to work with it in the same query as the parent table. You might find it easier to open the nested table in a new sheet, so you can work with it separately. This can help you avoid confusion and make it easier to transform and manipulate your data.

How to open nested tables in new sheets

To open a nested table in a new sheet, you can follow these steps:

1. In the Power Query Editor, select the nested table you want to open in a new sheet. You can do this by clicking on the table in the preview pane or in the Applied Steps pane.
2. Right-click on the selected table and choose Extract from the context menu. This will open the Extract dialog box.
3. In the Extract dialog box, choose Table from the Extract as drop-down list. This will extract the nested table as a new table.
4. Check the Add as new query option. This will add the extracted table as a new query in the Power Query Editor.
5. Click OK to extract the nested table and add it as a new query.
6. Rename the new query to something more meaningful. This will make it easier to find and work with the nested table in the Power Query Editor.
7. In the Power Query Editor, click on the new query to open it in a new sheet. You can now work with the nested table in the new sheet, separate from the parent table.

Example of opening nested tables in new sheets

Let's say you have an Excel workbook with two tables, one nested within the other. The parent table contains information about orders, and the nested table contains information about products. You want to open the nested table in a new sheet, so you can work with it separately.

To do this, you can follow these steps:

1. In the Power Query Editor, select the nested table you want to open in a new sheet. In this example, it's the Products table within the Orders table.
2. Right-click on the selected table and choose Extract from the context menu. This will open the Extract dialog box.
3. In the Extract dialog box, choose Table from the Extract as drop-down list. This will extract the nested table as a new table.
4. Check the Add as new query option. This will add the extracted table as a new query in the Power Query Editor.
5. Click OK to extract the nested table and add it as a new query.
6. Rename the new query to something more meaningful, such as Products. This will make it easier to find and work with the nested table in the Power Query Editor.
7. Click on the new query to open it in a new sheet. You can now work with the nested table in the new sheet, separate from the parent table.

In this article, we have shown you how to open nested tables from Power Query in new sheets. By following the steps outlined in this article, you can easily work with nested tables in the Power Query Editor, without having to deal with the complexity of working with them in the same query as the parent table. This can help you avoid confusion and make it easier to transform and manipulate your data.

References