Dynamically expand table column in Power Query (advanced)

TL;DR

Learn how to create a dynamic list of expanding column names in Power Query using nested environments and skip steps in the script.

Transcript

uh hello in this video we're going to show you a more advanced technique on expanding dynamic column names in our previous example we showed how we can easily create a dynamic function that is expanding column names so for instance we have these three tables and if we connect them through a different excel so first let's close this free table this ... Read More

Key Insights

• 💡 Expanding dynamic column names in Power Query:
• The previous example demonstrated how to create a dynamic function for expanding column names in Power Query, using the Table.ColumnNames function.
• To create a dynamic table expansion, drill into the first table, collect its column names using Table.ColumnNames, and paste this code to replace hard-coded values.
• To open an additional Excel file within Power Query, hold the Alt button and right-click on the Excel icon to open a new instance. From there, open another Excel file while still in Power Query.
• When adding a new column in the Excel source file, refreshing the Power Query immediately reflects the changes and appends the new column to the table.
• The shortcoming of this technique is that it only accesses column names from the first nested table. Adding a new column to a different table doesn't reflect in the expanded data.
• To overcome this issue, we can use a custom column, invoke the Table.ColumnNames function on each row or table, and expand the column names to new rows.
• After creating a list of all the column names, removing duplicates is necessary to obtain a distinct list for expanding column maps.
• By utilizing nested or inner environments, we can create clear and organized code, isolating specific steps for extracting distinct column names.

Questions & Answers

Q: How can I create a dynamic list of expanding column names in Power Query?

To create a dynamic list of expanding column names in Power Query, you can use nested environments and skip steps in the script. This technique allows for easy updating and expanding of column names when additional columns are added to the data source. The video provides a step-by-step guide on how to implement this technique.

Q: What is the shortcoming of the technique shown in the video?

The main shortcoming of the technique shown in the video is that it only accesses column names from the first nested table. If a new column is added to a different table, it will not be included in the dynamic list. However, the video also explains how to overcome this issue using nested environments and skip steps.

Q: How can I open an additional Excel file while inside Power Query?

To open an additional Excel file while inside Power Query, you need to hold the Alt button and right click on the Excel icon. This will allow you to open a new instance of Excel, from which you can open another Excel file while still being inside Power Query.

Q: How can I update my Power Query table when the data source changes?

If you make changes to your Excel file that Power Query is connected to, you can update your Power Query table by clicking on the refresh button. This will update the table and include any new columns or changes made to the data source.

Q: What are the benefits of using nested environments in Power Query?

Using nested environments in Power Query allows for better organization and control of the steps in your query. It helps create clear and modular code, making it easier to manage and modify complex queries. Nested environments are especially useful when dealing with multiple steps and complex transformations.

Summary & Key Takeaways

• The video demonstrates an advanced technique for expanding dynamic column names in Power Query.

• By using nested environments and skip steps, users can create a dynamic list of expanding column names.

• The technique allows for easy updating and expanding of column names when additional columns are added to the data source.