How to use Excel's power query to quickly transform data

in the practice of managing student achievement, the original data are often recorded by student entries (Figure 1). If the need to convert to school number, name, subject, grade, ranking of the table-type effect, the data volume, copy, paste operation is obviously quite cumbersome. The Microsoft Power Query add-in for Excel enables quick conversion (Excel 2016 already contains the Microsoft Power query component, which can be invoked directly on the Data tab, and other versions are required at Microsoft's official http:// Dwz.cn/2j93jh copy URL download installation).

1. Data Conversion to Table

Open the datasheet, switch to the Data tab, click the From Table button in the Get and Convert feature group, and then when the dialog box pops up to select the data source, Excel automatically converts the selection to a table and opens the Query Editor interface.

2. Extraction of disciplinary data

In the table 1-Query Editor window, select the study number, the column labels for the names, and switch to the Transformations tab. In the arbitrary columns feature Group, select reverse Pivot column → invert other columns, and after execution you can convert the currently selected column to a property/value pair and combine the remaining values in each row to display the effect as shown in Figure 2 ( Figure 2).

You can see that the properties column contains the names of many subjects, such as Chinese, maths, and English, and we first need to extract the names of these disciplines from the properties column. Select the list of properties columns, right-click to select Split → by separator, and then open the Split by Separator dialog box, select Custom in the Select or Enter Separator list box, and enter | Separated, there is no need to change other options, and the effect after execution is shown in Figure 3 (Figure 3). You can see that the original "property" column has been separated into attribute 1, property 22 column, "Property 1" is the name of each subject, "Property 2" is the score, ranking.

Because the property 2 column still mixes scores and rankings, you need to differentiate the content. Select the Properties 2 column, switch to the Transformations tab, and click the Pivot Columns button in the Any Columns feature group, to create a new column using the name in the currently selected column, the Pivot Column dialog box pops up, select value in the Value column Drop-down list box, and click the button in front of the advanced options Select Do not aggregate in the Drop-down list box, and the final setting is shown in the figure (Figure 4).

After you complete the above settings, the original Property 2 column has been converted to score, ranking two new columns. If you think the default property 1 is not appropriate, you can right-click to rename as discipline. When you close the Query Editor, you are prompted to keep the changes (Figure 5), you will return to the Excel main interface when you confirm, and you will see the final effect (Figure 6). Next can be based on the appropriate screening of each project, interested friends can try.

Extended reading: Quickly separating mixed text with power query

The "DATA" column shown in the figure is a mixed text of letters and numbers (Figure 7). It is now required to extract plain text, pure values, and not duplicate values sequentially. Manual extraction is obviously cumbersome and error prone, and can be easily implemented with functions built into power query.

Switch to the Data tab in the data area of the source data selection, when you click the From Table button in the Get and Convert feature group, you open the Query Editor, switch to the Add Columns tab, click the Add Custom Column button in the General feature group, and the Add Custom column appears. dialog box, first modify the default column name "Custom" to "plain text," and then enter "= Text.trim (Text.remove ([data],{0") in the Custom Column Formula pane below. 9 "})," "", after the check is correct, click the OK button, and you will soon be able to display the plain text content extracted from the DATA column in the newly added custom column, and continue to add the "pure value" and "No repeat values" two custom columns in the same way, with the formula "=text.remove" ( Text.trim (Text.remove ([data],{"A" ...) Z "})," ")," "" and "=text.combine (List.distinct (Text.tolist ([Pure value])"), note that the "pure value" of the latter formula must be consistent with the name of the previous custom column, or you will be prompted for "Error."

Close the Query Editor, and when you are prompted to keep your changes, select Hold, and after you return to the Excel main interface, you can see the detach effect (Figure 8).