Crosstab data consolidation using Power Query

Read the the article Consolidating crosstab data before reading this article.

In this article we understand how we can use Multi-column, Row Heading Cross Tab consolidation.

image

The problem

In the earlier example we saw that single row / column heading type of cross-tab data can be easily consolidated using the Pivot Table – Multiple consolidation ranges feature.

However, if the data has multiple row headings, this feature does not work.

image_thumb[1]

However, Power Query can be used for streamlining and consolidating this type of data.

Prerequisites

Perform a web search for EXCEL power query download and use the latest download link. This feature requires you to have Office 2010 or higher and Professional Plus Edition. How to find out which version of Office you are using?

Using Power Query UnPivot Feature

CrossTab Consolidation – PowerQuery.xlsx (25 kb) Download

The data has two columns for in row headers.

image

We still have four such blocks – for north, south, east and west. The first two columns do not have a heading. Those cells are blank. The data shown here is for the North region.

We will see how to create simple, tabular data from one block of this type of data. Once all these data blocks are converted to tabular data, we can do the consolidation within Power Query itself. Notice that first two columns have no headings. You can type Product Id and Product as the names for the first two columns.

Click inside this data and open Power Query tab and choose From Table. Now the data will be selected. Make sure that the selection is correct.

image

A create table dialog will appear. Click Ok to create the table.

image

Now the query editing window will appear showing all the data which was selected. In this case it is only the north region data.

Select the columns Feb, March, April and May, right click in the selected names and choose Unpivot option.

image

Now the data is instantly converted to tabular data without any manual work. Miraculous but true.

Eventually, we want to combine all four region data into a single block. Therefore, we need to identify each block of data with its Region name. While using Pivot Table consolidation, we used the Page field option to name each block.

Now the data is being converted directly to tabular form. Therefore, we will need to manually add a column called Region and fill it with the current region name – in this case North.

Right click on the Product Id column header and type the following name and formula.

SNAGHTML1633d641    SNAGHTML16364d9a

Specify North as the name for the query as well.

image

We do not want the data to be imported into Excel as yet. Therefore unselect both these options.

image

Repeat this process for all four regions. Now we have four different queries with nice tabular data and same columns. All that we need to do is to combine these query outputs into a single block which can then be analyzed using Pivot, Power Pivot, Power View or any other method.

Combining the query output = Consolidation

This is done using the Append Queries option. Go to the Power Query ribbon and choose Workbook button from Manage Queries button.

image    image

The right side pane shows all existing queries. “Load is disabled” is shown because we have not asked Power Query to submit the data either in the worksheet or in Power Pivot Data Model. We have just defined the queries.

Now right click on the North query and choose Edit. The query editor opens. Click on the Append Queries button from the Home tab of Query editor.

image

In the dialog choose South and click Ok.

image

Notice that the data of south query is now added to the North query.

SNAGHTML165972cb

A new query is NOT created. Just a new step is added to the North query.

image

Repeat this Append process with East and West as well. Now the data is fully imported into North query. If required you can rename the query to a generic name like AllData so that the query name is more representative.

Choose the Load to Spreadsheet or Load to data model option depending upon what you want to do next. Now you will get the consolidated data created quickly and easily for further analysis.

Inline append or Intermediate Append

We combined four queries together using the Append button from the Query Editor. This type of append operation adds a new step to the existing query.

image

I wish there was a way to multi-select queries so that instead of doing this 3 times we could have combined all queries in one step.

This type of appending of one query result into another query result is called Inline append operation.

In some cases you do not want to disturb the base queries. You want the result of the append to be a separate query. This is called an Intermediate Append operation. This is performed when you choose the Append option from the Power Query Ribbon (not query editor ribbon) or choose Append from the query context menu.

Data Sources

In this example the data came from Excel itself. However this approach will work even if data is imported from any other sources supported by Power Query. If you have multiple import queries, it is better to use Intermediate Append operations so that the original query is not affected.

Mobile phone

7 thoughts on “Crosstab data consolidation using Power Query

  1. Pingback: Combine Multiple Sheets into One Sheet | Efficiency 365

  2. Pingback: What did I learn today? Power Query Unpivot Other Columns | Efficiency 365

  3. Pingback: Instant, Interactive 3D mapping of your data – Power Map | Efficiency 365

  4. Pingback: Knowledge Pack: Data Accuracy in Excel | Efficiency 365

  5. Pingback: Knowledge Pack: Data Analytics | Efficiency 365

  6. Pingback: Power Query now available for standard version of Excel 2013 | Efficiency 365

  7. Pingback: Excel Tables 4 – Merged Cells and Cross Tab – The Data Villains | Efficiency 365

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s