In this post we will see how we can import data from Microsoft Word using PowerPivot’s copy-paste feature, create relationships on the data and perform aggregations that is BI! 🙂
For example, consider the two following tables which exist in a sample Word document:
The business query is to evaluate the sales activity for all the salesmen and decide possible adjustments to their commissions.
To do this in PowerPivot, we are going to use the copy-paste feature, create a relationship for the two tables’ data, and visually build our business query using the user-friendly interface and tools of PowerPivot.
The first step is to import the data from Word right into Excel using PowerPivot. So first, we have to launch Excel 2010 and then start the PowerPivot environment:
Then, we copy the first table from the Word document into clipboard and we click on the “To New Table” button.
After previewing the data and clicking on the “OK” button, we see that the first table’s data are imported into PowerPivot:
After the preview, we can see that the second table’s data are imported into PowerPivot:
Based on the sales activity of each salesman and on the results of the aggregations as illustrated in the two above charts, we derive to the conclusion that Salesman 3 should get the larger commission rate, then Salesman 1 followed by Salesman 2 and last Salesman 4 who has the less sales activity.
This post provided a simple example on how we can import data right into Excel using PowerPivot’s copy-paste feature, create relationships on the data and perform BI with ease by using the powerful engine of SQL Server PowerPivot.
I hope you found this post useful!
Artemakis Artemiou is a Senior SQL Server Architect, Author, and a 9 Times Microsoft Data Platform MVP (2009-2018). He has over 15 years of experience in the IT industry in various roles. Artemakis is the founder of SQLNetHub and TechHowTos.com. Artemakis is the creator of the well-known software tools Snippets Generator and DBA Security Advisor. Also, he is the author of many eBooks on SQL Server. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA).