Excel’s power tools — PowerQuery and PowerPivot — aren’t just for complicated analyses
Here’s a basic dataset that it worked wonders on!
The overview and analysis below shows the application of Excel’s power tools — PowerQuery and PowerPivot — to a basic, but robust dataset.
The point of this is to highlight that PowerQuery and PowerPivot ARE NOT just for big, complicated projects and data analysis efforts.
Everyday analysis is simplified by just obtaining a basic understanding of the Power Tools. 💣
Starting point…
👉 My financial advisor maintains a dataset, in Excel, that reflects investments at the Individual holding level (e.g., individual equities, mutual funds, bond funds, etc
👉 This dataset contains monthly data for the last 5 years
👉 Each record reflects specific attributes of the holding, but, in many cases, it is still to granular for the summary level analysis I want to get to
Enter Excel’s power tools — PowerQuery and the PowerPivot Data Model…
In two hours, I imported the holdings detail and built the following:
👉 Cleaned the Holdings Data, eliminating extraneous fields I didn’t need and properly formatted the data types
👉 Built a Dynamic Date Table (in PowerQuery rather than DAX) to increase flexibility of data for analysis
👉 Created two “lookup” tables that will enable summarizing the data at a higher level (e.g., Equities, Fixed Income, and Cash)
👉 Created relationships between the FACT (or transaction) table (Holdings) and the DIMENSION (lookup) tables I created
👉 Added three DAX measures — Total Market Value, Average Market Value, and a COUNT of Holdings. This provides significant benefits for analysis and Pivot Table purposes.
👉 Finally, I hid four Fields in the Holdings table from the Client View so that they don’t appear in the Pivot Table field choices (simplifying the Pivot Table options and creation)
Here’s just a few of the benefits:
1️⃣ No changes to the underlying dataset. Going forward, that is maintained as it has been.
2️⃣ No formulas necessary. No VLOOKUPS, etc. Multiple tables relationships exist.
3️⃣ Pivot Tables can be developed using multiple tables.
4️⃣ Explicit DAX Measures are much more flexible than allowing the pivot tables to perform the calculations. Formatting carries over to any pivot tables created.
5️⃣ An additional benefit of building this Data Model in Excel is that it can be imported directly into PowerBI, enabling easy preparation of advanced visualizations of the data!
If these tools are new to you, just dive in. The water is warm. 😀
