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

Original title: "Can you super pivot table?" More than 10 times better than Excel pivot tables!

Hello everyone, I am studying the small cool of table structure conversion ~

Before we talked about using conventional methods to make secondary drop-down list auxiliary table: making secondary drop-down list essential, this wonderful method, you may not have seen it!

Today we will introduce the M function approach of Dax metric (Power Pivot) and Power Query, mainly to expand your thinking.

As shown below, we now need to convert the left table to the right schema.

Using the Dax metric, let's first look at the operation:

Create an auxiliary column with the formula:

=COUNTIF($B$2:B2,B2)

Insert Pivot Table and check Add this data to data model.

PS. We want to use Dax, just check Add this data to data model when creating Pivot table.

Click the Pivot table area, and under the Power Pivot tab, click Measure-New Measure.

Enter a formula in the Formula bar with Measure Name Measure 1.

=CONCATENATEX ('region ',' region'[subclass])

In the formula, the region is our data source table and the region [subclass] is the subclass column in the region table.

The CONCATENATEX function combines multiple texts together, similar to the TEXTJOIN function in Excel.

The CONCATENATEX function has the following structure:

=CONCATENATEX(table, expression, delimiter)=CONCATENATEX ('region ',' region'[subclass]) So the Dax function formula above means text merging the subclass columns in the region table.

Place auxiliary columns in the row area, large classes in the column area, and measure 1 in the value area.

Disable totals rows and columns.

Here, the production is complete.

There may be some doubts about Dax, so let me briefly introduce it.

Traditional Pivot tables cannot pivot text, but with the advent of Power Pivot we can do so with Dax measures.

Power is super, so Power Pivot is super pivot.

DAX is an abbreviation for Data Analysis Expression, and Dax is a data statistical function used on the basis of Power Pivot.

The benefits of using DAX are:

can make up for many defects of Calculated Field in Pivot Table.

The Dax function modifies the method of aggregation calculation.

In a normal Pivot Table, there are only a few ways to summarize values, such as summation, counting, etc.

In Power Pivot, more flexible aggregation can be achieved through multiple Dax functions.

For example, in this case, we use the CONCATENATEX function to merge text.

PowerQuery is a powerful tool for data cleaning and data transformation. Now let's take a look at how to use it to achieve the desired effect.

Specific steps:

Import data into PQ editor.

Select data area-under the Data tab, select From Worksheet-OK to enter PQ Editor.

Select a large category column. Under the Home tab, click Group By-All Rows-OK.

PS: The grouping function is to group statistics on data. What we want here is to group large categories. At the same time, what we need to summarize items is a list formed by small categories in large categories.

Change the M function formula to each [subclass].

Click [fx] to add a step and enter the formula:

= Table.FromColumns(grouped rows [count], grouped rows [large class])

The Table.FromColumns function converts columns into a list of columns.

= Table.FromColumns(list of lists formed by titles)

In the case:

= Table.FromColumn (lists formed by grouped small classes, list of heading large classes)

So the formula is,

= Table.FromColumns grouped rows [count], grouped rows [large class]) Close and upload the table.

At this point, the PQ method is complete.

To sum up, this article introduces the extension method of the secondary drop-down list auxiliary table:

Use Dax metrics:

Countif function is used to count the number of times it appears.

Create a new measure, and the CONCATENCEX function merges text.

Large classes are column areas, secondary columns are row areas, and measures are value areas.

Using PowerQuery:

Basis for grouping.

Table.FromColumns。

Have you learned how to make the auxiliary table of the secondary drop-down list ~

This article comes from Weixin Official Accounts: Autumn Leaf 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.