PART 4 : How to Build a Live Excel Financial Dashboard 📊 Connected to AppSheet 🔗

🔄 Transform Accounting Data in Excel Using Power Query (Part 4)

In Video 4 of the AppSheet & More series, we focus on data transformation and modeling in Power Query, preparing our accounting data for accurate financial reporting in Excel.

After connecting our data in the previous video, this session is all about cleaning, shaping, and structuring data the right way — all inside Power Query, without writing code.

CHAPTERS;
Intro - 0:00
Querying GL table - 0:50
Creating a Master COA - 3:02
Cleaning our GL table - 11:05
Creating a Calendar Table - 13:17

🛠 What you’ll learn in this video:

How to clean and transform accounting data using Power Query

How to remove unnecessary columns and keep only required fields

How to merge multiple Chart of Accounts (COA) tables in Power Query

How to combine related lookup tables for reporting

How to create a calendar (date) table using Power Query

Why a calendar table is critical for time-based financial analysis

🔍 Key Power Query Concepts Covered:

Column filtering and data cleanup

Merging and joining tables in Power Query

Building reusable, refreshable queries

Preparing data for financial statements and dashboards

By the end of this video, you’ll have:
✅ A clean, transformed accounting dataset
✅ Unified COA data ready for reporting
✅ A dynamic calendar table for month, year, and period analysis

This step ensures that when we move into building financial statements and dashboards in Excel, the data is already structured for performance and accuracy.

This tutorial is part of a collaboration with ‪@FinexSkillsHub‬ one of Ghana’s leading data analytics training hubs, and belongs to the AppSheet & More series — focused on real-world Power Query and Excel analytics.