Скачать или смотреть How to Dynamically Load Only the First 20 Columns in Power Query

Learn how to efficiently manage data in Power Query by dynamically loading only the first 20 columns of your sheets, avoiding issues with excessive blank columns.
---
This video is based on the question https://stackoverflow.com/q/66640410/ asked by the user 'David Bock' ( https://stackoverflow.com/u/15401150/ ) and on the answer https://stackoverflow.com/a/66641877/ provided by the user 'Alexis Olson' ( https://stackoverflow.com/u/765226/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Power Query only load first 20 columns

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Efficiently Load Only the First 20 Columns in Power Query

If you're working with Power Query and Excel, you may encounter a situation where your data sheets contain a plethora of blank columns that inflate the size of your resulting tables. This can lead to performance issues and difficulties in managing your data effectively. In this guide, we will address the issue of only needing to load the first 20 columns from your data.

The Problem: Excessive Columns in Power Query

When you use Power Query to import data from Excel sheets, you might run into the scenario where your code inadvertently loads numerous blank columns. Here are the key points to understand the problem:

Messy Sheets: Often, you may work with sheets that are not in the best format, like those with millions of blank columns due to company requirements.

Inflated File Size: Importing all these empty columns can result in very large tables, causing slow performance and making data manipulation cumbersome.

Static Loading: The default importation method loads all data, which is not necessary if you only need the first few columns.

The Solution: Dynamic Column Selection

To efficiently manage your data import into Power Query and only load the essential columns, you can modify your existing code. The solution involves adding a new step that dynamically selects the first 20 columns from the imported data.

Step-by-Step Implementation

Locate Your Import Statement: Find the import statement in your code where the data is being loaded.

[[See Video to Reveal this Text or Code Snippet]]

Add the Dynamic Column Selection Step: right after the import statement, add the following line of code to select only the first 20 columns.

[[See Video to Reveal this Text or Code Snippet]]

Complete Code Integration: Your entire code should look something like this:

[[See Video to Reveal this Text or Code Snippet]]

Explanation of the Code

Table.SelectColumns: This function is used to select specific columns from a table.

List.FirstN: This function helps you get the first N elements of a list; in this case, the first 20 column names.

Table.ColumnNames: This retrieves a list of all the column names from the table.

By following these steps, your Power Query will only load the first 20 columns, making your dataset manageable and improving performance.

Conclusion

Managing data using Power Query doesn’t have to be a complicated affair. With the right adjustments, you can easily load only the necessary columns and enhance your data handling efficiency. The implementation of Table.SelectColumns and List.FirstN is a powerful solution to trim down excess data loads.

Now that you have the solution, you can apply it seamlessly to your projects and avoid the difficulties that come with excessive column loading. Happy querying!