Скачать или смотреть How to Add Multiple Columns in One Step in Power Query

Learn how to efficiently add multiple columns in one step using Power Query with this simple guide and a custom function solution.
---
This video is based on the question https://stackoverflow.com/q/77314808/ asked by the user 'Jomelar Ongcay' ( https://stackoverflow.com/u/22713341/ ) and on the answer https://stackoverflow.com/a/77314972/ provided by the user 'davidebacci' ( https://stackoverflow.com/u/18345037/ ) 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: Multiple Columns in One Step in Power Query

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.
---
How to Add Multiple Columns in One Step in Power Query

Managing data effectively is crucial for any analytical work, especially when using Microsoft Excel or Power BI. Sometimes, you need to add multiple columns to your dataset based on existing data. This guide will guide you through the process of adding multiple columns in one step using Power Query—a powerful data transformation tool.

The Challenge

You’ve likely faced the challenge of adding multiple columns to a Power Query table with values derived from an existing column. For instance, you may want to create new columns named NewCust1 and NewCust2, both populated with data from an existing column called Source.Name.

Initially, you might have found a solution that introduces two new columns filled with null values, but this isn’t the desired outcome. The aim here is to ensure both new columns reflect the content of Source.Name.

Here’s how to approach overcoming this challenge.

The Solution

Instead of using a cumbersome method, there’s an easier and more efficient way to achieve adding multiple columns at once. Below, we outline the method using the Power Query M language.

Step-by-Step Instructions

Here’s how the final solution looks in Power Query:

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

Breakdown of the Code

Source Definition

The Source step initializes the table from the rows of an existing data structure ensuring it's correctly formatted.

Change Data Types

With Table.TransformColumnTypes, we specify the types for existing columns (Column1 and Column2) to ensure accurate data management.

Adding Custom Columns

The core step here is Table.AddColumn, where we create a new column called "Custom".

Here, we define NewCust1 and NewCust2, both set equal to the values from Source.Name. This is crucial for fulfilling the requirement of populating these new columns based on Source.Name.

Expanding the New Columns

Finally, Table.ExpandRecordColumn is used to make the new columns visible in the final table, completing the process.

Conclusion

Adding multiple columns in one step using Power Query doesn’t have to be complicated. By leveraging the M language effectively, you can streamline your data transformation tasks. This approach not only saves time but also enhances the efficiency of your data processes.

Now you've learned how to create new columns that draw values from an existing column easily. Happy data transforming!