You have data in several text files, altogether three reports, each of them contains 100 lines. The structure is the same in all the three reports: sales representative, product and sales volume.

Note, that the data is in line with header row, even though file looks bit skewed.

What is the total sales volume for each product ?

We could find this out by copy pasting three reports into one Excel sheet and placing pivot on top of the combined sheet.

However, sales volume by product can be calculated with power pivot.

First, we need add them all to worksheet’s data model.

As the files are in one folder C:\BI, we can transfer the whole folder to data model.

Select data – get data – from file – from folder.

Browse the correct path.

Press Transform data.

The combined files are listed.

Press combine files -button with two arrows downwards in content column.

Looks ok, press ok.

All 300 lines are included.

Select Close & Load – Close & Load to.

Select only create connection and activate add this data to the data model. Press ok.

The queries and connections. Also BI-folder with 300 records is included.

If you don’t see this box, select data – queries & connections.

Select  Power Pivot – Manage to see the data model.

Data is there.

Before we dive into Power Pivot, we could create a measure for the sum of sales.

DAX is short =sum(BI[Sales]) . Format is whole number as sales is not a decimal number.

Time for pivot: take insert – pivot table.

We use worksheet’s data model as the source data.

Source.Name is a new field, which is the file name either one.txt, two.txt or three.txt. Take product as rows and sales total as values.

The result.

Of course, you can append text files manually, and then run normal pivot.

Append manually three files into one.

Use the range as the source data.

The result is the same with traditional pivot.

Combining files from folder into data model took more time than traditional pivot. If you had twenty text files each containing 10000 rows, that would take long time to combine all manually. Also manual process increases the possibility for human error.

Data model makes it possible to create relationships between tables and expand report.

If you can download the data with separate files with same structure, it is not an issue for power pivot reporting.

You have downloaded a simple sales report from ERP.

The report includes product data, region, sales representative, sales amount and margin.

You could easily create a Pivot table using the range A1:E28 as source data.

Another option is to create a table and add the table to data model.

Activate the range manually or control + A. Then select insert – table or press control + T.

Press ok. Check that My table has headers tick box is activated,  as the first row includes header information.

Layout changed a bit, when table was created based on the range.

Choose power pivot – add to data model.

Data is now in Power pivot data model.

Table name was changed to more descriptive Tr sales. Tr stands for transactions.

Create a pivot based on workbook’s data model. The traditional pivot would be used when selecting the first radio button, now we use workbook’s data model as source data for pivot.

Report the margin per sales rep based on data model.

The results.

This could have been done with normal Pivot, too.

However, both products and sales reps are just codes in the transaction table. To make report more informative, codes could be replaced by real names.

Master data table for sales reps. Table includes name, day for starting the employment and boss’s name.

Make the table by activating the area and pressing control + T. Then press ok. Select Power Pivot – add to data model. Table name was changed to md_rep.

Note that the data is imaginative.

Master data table for products include product name, size of the product and product division.

Table was made also from the product table. Table name is md_prod.

Relationships in data model. Tr sales-product and Tr sales-rep are foreign keys in transaction table. Corresponding primary keys in master data tables area md_rep-rep and md_prod-product.

Cardinality is many in transaction table and one in master data tables.

Now we can start actual reporting.

To see how two sales teams have been doing by product division. Remember to select all the tables, not only active ones.

If you pivot field selection looks different, press

and select Field section area section side-by-side.

A calculated column created to count the margin percentage.

It took more time to transfer the report and the data to data model than using the traditional pivot table. However, data model creates basis for more business intelligence type reporting. Once master data is in place, you can use those with other transaction tables. Data model gives you countless number of possibilities to understand the data. Only few examples were shown here.