Shulou(Shulou.com)11/24 Report--

The original title: "which Excel master figured out this data collation skill, too useful!" "

Xiao E made a "live surprise event", and there were many express deliveries to be sent after the end.

In order to save logistics costs, he confidently told his boss that he could consolidate the same order data and ship the goods together.

On hearing this, the boss decided to leave the whole process to him. That's great. Poor little E not only has to make data, but also has to pack a bunch of couriers, which is a great headache.

In the previous article, we shared the function and square grid plug-in method.

Today, Xiao Shuang will introduce you to other methods to see how we can solve this problem with PQ.

PS:PowerQuery (PQ for short) is a plug-in that comes with Excel 2016 and above. M function is the name of the function in PQ.

Due to the situation that there are multiple order data in the same information, in order to simplify the difficulty of the problem and facilitate everyone's understanding, let's first make a summary auxiliary table.

Warm Tip: the practice of PQ involves several basic M functions, but it is not difficult.

Grouping according to the use of PQ, naturally, you need to import the data source into the PQ editor first!

Select the table area of the auxiliary table, under the data tab, click from Table / region, and click the OK button.

After entering the PQ editor, the next step is to group.

For the grouping problem in PQ, we use the grouping function.

What is the grouping basis of 👉? The function of grouping is a bit like a PivotTable, which filters and statistics the data of specified fields. It can specify multiple fields as conditions, or it can count multiple results at the same time.

Grouping according to the statistical methods that can be supported include: summation, average, minimum, non-repetitive row count and all rows.

After the knowledge has been added, everyone follow me to take a look at the operation.

Select the columns that need to be grouped, that is, our name and mobile phone columns, and click Group by under the conversion tab.

The grouping by dialog box appears.

New list: shipping quantity

Actions: all Lin

At this time, we can see that after the form is grouped by name and mobile phone number, there is an additional shipping quantity column.

The Table of each row in the shipping quantity column contains the filtered table data for the current row.

The content following the grouped type type after merging by line has no effect on the overall situation and can be deleted for the sake of code brevity.

After deletion, the formula is more concise.

= Table.Group source, {"name", "mobile number"}, {{"shipped quantity", each_}}) the underscore in each_ represents the data of the filtered table in each row.

Then, as shown in the following figure, we need to merge the product name and the total number of products in the filtered data table with an asterisk (*) in the column of shipments after grouping.

As shown below:

= Table.Group source, {"name", "mobile phone number"}, {{"shipment quantity", eachTable.ToList (_, (x) = x {2} & "*" & Text.From (x {3}))}})

The Table.ToList (_, (x) = x {2} & "*" & Text.From (x {3})) Table.ToList function mainly processes the product name and the total number of each row in the table.

Among them

The 2nd product name in each row index

The total number is the third of each row index.

What is the 👉 Table.ToList function? Table, which means table, and ToList, means transformation list. This function is used to process each row of data.

Table.ToList has two parameters:

= Table.ToList table, (x) = x) x represents the list formed by each row

Little Tip:

I made it a little more difficult above, in fact, we can also merge the product name and the total number in the auxiliary table in advance. It should be easier for people to understand.

After importing the auxiliary table into the grouping, you can simply take the merged columns.

The last step of list merging is to merge the merged product name and the total number of products, and the whole list is separated and matched.

On the previous basis, you need to use the Text.Combine function to merge list with commas.

What is the 👉 Text.Combine function? Text, is the meaning of the text, Combine, is the meaning of combination. This function is used for text connection!

Text.Combie has two parameters:

= Text,Combie text sequence delimiter

In the case, add a Text.Combie:

= Table.Group source, {"name", "mobile number"}, {{"shipment quantity", eachText.Combine (Table.ToList (_, (x) = x {2} & "*" & Text.From (x {3})), ",")}})

If there is an auxiliary table for juxtaposition.

= Table.Group Source, {"name", "Mobile number"}, {{"Shipping quantity", eachText.Combine ([merge], ",")})

It's done here.

If you don't want to have auxiliary tables, you need two-tier grouping, and the idea is the same as before, that is, add an extra Table.Group grouping, and those who have more energy to learn can try.

Let source = Excel.CurrentWorkbook () {[Name= "Table 1]} [Content], lines grouped = Table.Group source, {" name "," mobile phone number "}, {{" shipped quantity ", eachText.Combine (Table.Group_, {" product name "}, {{" a ", (x) = Text.From (List.Sum (x [quantity of goods])}}), (y) = y {0} &" * "& y {1}),"; "}) rows grouped by in

Finally, this paper mainly explains the problem of "how to solve the problem of complex merging similar items with PQ". In order to simplify the problem, an auxiliary table is made in this paper.

The following knowledge points are involved:

❶ grouping is based on the ability to summarize and consolidate multiple fields. If you want to return all rows, you can select all rows in the operation.

❷ Table.ToLIst can further manipulate each row of data in the table.

❸ Text.Combine is mainly by specifying the separation of the union list.

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: Xiao Shuang

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.