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 data consolidation using Power Query.

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.

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

Prerequisites: Data consolidation using Power Query

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

The data has two columns for in row headers.

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.

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

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.

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.

   

Specify North as the name for the query as well.

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

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.

   

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.

In the dialog choose South and click Ok.

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

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

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.

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.

— —