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

Every year, the company's administrative personnel department will plan the company's activity funds and review the budget.

The amount of funding for activities may vary from department to department in different months.

Activity expenses refer to the various expenses required to hold activities. For example, the organization to participate in the activities of personnel funds, venue funds, equipment funds and so on all the costs for the activities.

To facilitate registration, viewing, and printing, they will make the form look like the following picture.

This looks more intuitive, and some cousins also like to make tables in this style.

However, it may be more appropriate to analyze the allocation of funds for activities more quickly, organize them into an one-dimensional table format, and then use PivotTable analysis.

So how to sort out the above data?

If you are Office365, you can directly use the Vstack function to concatenate different data regions in a vertical direction.

= VSTACK January, February, March, April, May, June, July, August, September, October, November, December) Note: the corresponding area has been named in advance.

If you are not an Office365, it is difficult to do it by using the traditional Excel function directly. At this point, you need to use the sharp weapon of data collation-PoweQuery.

Now let me briefly introduce its practice.

Warm Tip: this article will involve several simple M functions, not M function partners do not have to worry, roughly have an impression.

PQ requires Office2016 and above. The M function is the function used in PQ.

1. For the specific operation, we preprocess the table in advance, import the table, filter and remove the header and null (null) value from the column.

❶ imports the data into the PQ editor.

Select all the data areas, in the data tab, select [from Table / region]-create Table-[OK] to enter the PQ editor.

❷ click the bottom corner of the department filter, uncheck "Department" and "null", and click the "OK" button.

For multi-region tables, with PowerQuery, we can merge in a variety of forms, and the practice I describe below is to merge in the way of each row.

Next, we do the table conversion process.

Step 1: change the line to List

With Table.ToList, each row in the table is formed into a List.

Table.ToList: forms a List from the table in the row direction

= Table.ToList table, each _) forms a list of each row in the table, and each _ can take further actions on each row. In the following figure, for example, each row in the list in the result returned by the formula is the data corresponding to each row in the table.

Step 2: remove the null value from each row

List.RemoveNulls: remove null from the list

= List.RemoveNulls list) remove the null value from the list step 3: split each list

We can see that between each List, there are three sets of data.

So we directly use the List.Split function to split the data.

List.Split: list split

= List.Split list, several at a time) Split means to separate, and List.Split means to split the list according to every N to form a separate List.

Step 4: turn the table

Convert each List to the table by row, here we use the Table.FromRows function (you can also use Table.FromList).

Table.FromRows: a table that converts a list formed by list into a row orientation.

= Table.FromRows (list formed by list, converted to the corresponding field of the table) if the second parameter is not written, the default table title is Column1,Columns2... How can we get the title of the table we need?

To get the title row, we can first use Table.ColumnNames to get the List of the title, and then List.FirstN to take the first three headings.

Table.ColumnNames: get the title in the table

= Table.ColumnNames table) you can get all the headings in the table and return a List

List.FirstN: get the top N in the list

= List.FirstN list, top N)

We will paste the title function on the second parameter of the Table.FromRows function, and at this point we have completed the transformation after the split, and finally we can merge the tables.

Step 5: merge

Finally, we use Table.Combine to merge, and that's it.

= Table.Combine (rows filtered by Table.ToList, each Table.FromRows (List.Split (List.RemoveNulls (_), 3), List.FirstN (Table.ColumnNames (source), 3)

Table.Combine: merges multiple Table tables in a list

= Table.Combine List formed by multiple Table)

Upload the processed data to the table.

2. Extension what is mentioned above is to transform and merge according to each row, so what should be done to merge according to each column?

In fact, the idea is the same as before, except that the function used is slightly different.

As shown below:

❶ converts each column in the table to a list (Table.ToColumns)

❷ removes the null value (List.Select)

❸ is split every 3 columns (List.Split)

❹ list loop (List.Transform), turn the table by column (Table.FromColumns)

❺ final merger (Table.Combine)

= Table.Combine (List.Transform (List.Split (Table.ToColumns filtered rows), each _ {0} null), 3), each Table.FromColumns_,List.FirstN (Table.ColumnNames (source), 3)

3. What is written at the end of this article is to merge the data of multi-area tables with the same interval.

It is possible to copy and paste manually, but it cannot be updated automatically after the data is changed.

Using the new Vstack function in Office365, we can directly splice multiple regions vertically. However, due to version restrictions, most people do not have this function.

Data collation, the most commonly used tool is PowerQuery. With it, the basic interface operation can do a lot of finishing work, but if it is a little more complicated, you need to lose the M function.

This article may be a little difficult for friends who don't have the basis of M function, but we don't really need to do this if we have a data specification.

Therefore, it is usually best to standardize the records of data, so that a lot of unnecessary work can be reduced.

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

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.