You might have seen three dimensional Excel formulas.

Product one.

Product two.

Product three.

Each worksheet holds sales data by sales region and by month.

Each worksheet presents sales data per product. The reports above cover the products from one to three.

There are three dimensions: month, sales area and product. Just two dimensions, month and sales area, were presented in one worksheet.

You can create SUM functions across the three worksheets.

Total sales for North area or total sales for the month August can be calculated manually. This is not convenient for ad hoc queries.

Better way would be to have data in pivot format. Then month, region, product and sales amount should be separate columns.

For example:

Month  region   product              sales

Jan        North    P1                        23

And so on.

Add a column for the product, P1 in the sheet above, in all the three worksheets.

Take data into power query per each worksheet.

Activate the columns with header, meaning the sales areas and press right mouse button. Select unpivot only selected columns.

I added names for new column names  month, product, area and sales.

Repeat this for each worksheet P1, P2 and P3, that the sheets have the same column names in the same order.

Select append queries. The idea is to combine all the queries as one long query.

Press ok.

Import the data into data model. Create a pivot on top of data model.

The August sales is 197.

Sales for North area is 587.

When we have power query and power pivot, we can forget three dimensional worksheets.

In this example, I did not write even any DAX. It is possible to have self made measures with DAX, but it just was not needed to count total sales for August and North area.

A good thing is that the same process works also in Power BI.

My Power BI is in Finnish, but the idea is the same as in Excel.

In Power BI Aug sales is 197.

The same results in Power BI.

When a process exists in power query, you can use that both in Excel and in Power BI.

If three dimensional Excel worksheets were once used, nowadays are power query and power pivot very convenient. As said, you can use either Excel or Power BI.