How to split data to NTFS using Power Query

This article will introduce how to use Power Query to split data into rows. When exporting data from other systems or sources, it is common to encounter situations where the data is stored in cells combining multiple values. With Power Query, we can easily split such data into rows, making the data easier to process and analyze.

This can happen if the user doesn't understand Excel's rules and accidentally enters multiple data into a cell, or if the data is not formatted correctly when copying/pasting it from other sources. Processing this data requires additional steps to extract and organize the information for analysis or reporting.

How to split data in Power Query?

Power Query transformation can split data into different cells or rows based on a variety of different factors such as strings, text patterns, and data types. You just need to select the column containing the data you want to split and then use the "Split Column" feature in the "Transform" tab in Power Query Editor to easily split the data. In this article, we will explore in detail how to split data into rows using Power Query.

How to split data into rows using Power Query

To use Power Query to split data into rows, you need to perform the following steps:

Suppose our data is in the form of a text string containing a name and email address separated by a semicolon. For example, you can refer to the screenshot example below.

Right-click the cell containing the data and select "Get data from table/range" from the context menu.

Click OK in the Create Table pop-up window and make sure the "My table has heads" option is unchecked. The data will open in Power Query Editor.

Split data into databases through Delimeter

By default, Power Query treats the entire cell content as a single value and loads it as a single record with only one column and one row. To split the data, go to the Split columns menu in the Home tab and select By delimiter. This option allows you to split the values ​​in a selected column based on a specified delimiter. The remaining options in the menu allow you to split column values ​​based on other factors, such as a specified number of characters, position, or transition.

After clicking "By Delimiter", a Split Column by Delimiter window will appear. Select a string (a character or sequence of characters that separates and distinguishes individual values ​​in a data set) in Select or Input String. Since in our case, semicolon separates the data values, we selected semicolon from the semicolon menu, as shown in the screenshot below:

Next, click and expand Advanced Options and select Split into ‘Rows’. Click OK to apply changes.

Power Query will now parse your data, splitting it on each occurrence and placing each entry on its own separate row.

As you can see in the screenshot above, our data has been nicely split into rows. Again, we'll split the data to separate names from email addresses.

Split data into columns through Delimeter

Click the "Split Columns" button under the "Transform" section in the "Home" tab and select "By Separator". The Split Columns by Delimiter window will appear.

For Power Query to convert data correctly, delimiters must be correctly identified and specified. For example, in our example, use a less-than sign (&) to separate the name and email address. So we'll select Custom in the Select or enter delimiter dropdown and type the less than sign (in most cases, Power Query will automatically try to determine if your data contains values ​​separated by a delimiter, and if so, that What is the separator?).

This time, we will not click the "Advanced Options" button because we want to keep the default value, which is Split into "Columns". Click OK to apply the changes.

Power Query will transform your data and place names and email addresses in separate columns, as shown in the image below:

Note:

• You can perform additional steps to remove spaces or extra characters from your data. For example, in our example, each email address is followed by a greater than symbol (). >To get rid of it, we will right-click on the column header and select Replace Values. Then replace "" with " (enter "" in the "Value to find" field and leave the "Replace with" field blank).
• You can give each column a descriptive name by double-clicking its title.

When finished, click the "Close Loading" button in the upper left corner of Power Query Editor to export the data in a new Excel worksheet. &

that's all! I hope you find this useful.

Read: How to convert columns to columns using formulas in Excel.

How to convert columns to rows in Power Query?

Select the columns to convert to rows. Go to the Transform tab. Then go to the Unpivot Columns menu in the Any Column group and select Unpivot Selected Columns Only. Power Query will convert the selected columns into rows and create two new columns, one for the attribute name and another for its corresponding value. If necessary, rename the columns and click the Close and Load buttons to save changes to the data.

Read Next: How to Group and Ungroup Rows and Columns in Excel.

The above is the detailed content of How to split data to NTFS using Power Query. For more information, please follow other related articles on the PHP Chinese website!