Articles and Checklists

Following my recent post, "Power Query: A Missed Opportunity for Many Finance Teams," I wanted to explore one of Power Query's most underrated strengths: its ability to check, review, and audit data quality long before the data reaches your warehouse or reporting models.

Quality data sits at the foundation of all analysis, insights, and AI. Just like any process, the end result can only be as strong as the raw material feeding it—your data.

If you start your journey with Power Query, one of the biggest advantages you gain—often unintentionally—is powerful data profiling and quality assurance. Traditional spreadsheets make data quality checks painful, manual, and often ineffective. But with Power Query, it becomes a breeze.

With its built-in profiling tools, you can scan entire datasets in seconds and quickly identify issues such as null values, duplicate records, inconsistent data types, and error rows. Addressing these problems at the ETL stage is crucial—if left unresolved, they can have a significant negative impact downstream in your data models, reports, and AI outputs.

Here's how Power Query enables this:

🔍 Column Quality

Instantly view the percentage of valid, error, and empty values in each column—providing a clear snapshot of data health.

📊 Column Distribution

See how values are spread across a column—identify distinct values, duplicates, and unexpected data patterns with ease.

📈 Column Profile & Statistics

Access deeper insights into data structure, including:

• Row count
• Distinct vs. unique values
• Min/Max
• Value distribution
• Data type consistency

These features equip finance teams with the ability to understand, validate, and prepare clean, trustworthy data—before it ever reaches a dashboard or data model.

Have questions or comments about using Power Query for data quality? We'd love to hear from you.

Today I had an enriching conversation with a finance team exploring how to begin their journey toward reporting automation, analytics, and AI. What impressed me most was how clearly their manual processes are defined and how knowledgeable the team already is—an excellent foundation for automation.

We explored the "low-hanging fruit" available through Power Query, a capability many finance teams still overlook. Power Query enables powerful data extraction, transformation, and workflow automation directly from Excel which most finance professionals are already familiar with.

When someone asked how to access Power BI to start their journey, I shared that it's typically already included in most Microsoft 365 subscriptions—often all that's needed is a quick discussion with the ICT team to activate the Power BI Pro license.

I suggested the following roadmap to get started:

A structured and intentional investment in data and AI literacy is essential. Without a solid foundation in data analysis, finance teams will struggle to maintain, optimise, and continuously improve their BI and reporting platforms. The setups can always be done with external expertise but maintenance and optimisation is better done in-house.

Have questions or comments about Power Query adoption in finance teams? We'd love to hear from you.

Every now and then, something breaks on my reporting platform (Power BI). These breaks are less frequent than before, but when they happen, they're frustrating. Over time, you learn to anticipate or quickly fix issues—but some challenges still sneak through.

Recently, I spent three days trying to fix a forecast calculation. The logic requires different assumptions for different account groups, and despite trying every trick in the toolbox (yes, even ChatGPT and Claude), I was stuck.

As I was running out of time, I resorted to perform a manual calculation in a spreadsheet. It felt like defeat. I then created some simple measures (building blocks) for the manual calculation and realised I could use the measures in a SWITCH DAX measure to determine the forecast.

That experience left me with some important reminders:

Data architecture matters as much as the calculation itself. If your measures feel overly complicated, it's often a sign that your data model or structure needs attention.

Simplicity is key. Complex formulas quickly become black boxes. They're harder to explain, harder to trust, and riskier to maintain.

The principles are even more important when using AI.

Have questions or comments about data architecture and modeling? We'd love to hear from you.

Business Intelligence (BI) tools have transformed how organisations approach reporting and analytics. However, as with any new development, challenges can arise during implementation, and businesses may struggle to achieve the expected Return on Investment (RoI). In my experience, change management, early involvement of key users, and solid support from technology providers — including internal ICT — are key to success.

Many BI discussions and posts focus on dashboards, which are indeed important for communicating insights and performance, particularly at a strategic level. While that's valuable, I've found even greater impact from using BI tools for operational purposes — driving efficiency, accuracy, and cost savings.

Here are a few practical operational use cases where BI can make a real difference:

👉 I'd love to hear how others are applying BI tools in operational contexts. What are your favourite use cases?

Have questions or comments about using BI tools beyond dashboards? We'd love to hear from you.

If you're building or maintaining Power BI reports, you'll eventually hit the wall of data refresh failures. Whether it's a manual trigger or a scheduled refresh via the Power BI Service, there's always potential for something to go wrong.

Over time, you learn that troubleshooting is part of the game. Here are just a few common culprits:

🗂️ File-related changes (Excel, CSV, etc.):

• File name or sheet name changed
• File structure or format updated (e.g., new columns added, removed, or renamed)
• File moved to a new folder or network location

🔐 Security or Access Issues:

• Privacy level mismatches
• Credential problems (expired tokens, changed passwords)

🧩 Underlying Data Model Changes:

• Table relationships broken
• Deleted or renamed columns
• Data type mismatches

The old adage goes: "Experience is the best teacher," but I'll add — experience + ChatGPT = a real time saver. Still, sometimes, no matter how smart the AI or how seasoned you are, a stubborn error can stop you cold.

In my case, the "breakthrough" came when I started refreshing the tables manually one by one in Power BI desktop until I figured which table was causing the error. My data model has a lot of tables and I was struggling to establish which table was causing the error.

What has been your experience?

Have questions or comments about Power BI troubleshooting? We'd love to hear from you.

The proliferation of tools and services available for Business Intelligence (BI), data analytics and dashboards provides organizations with a wide array of options to improve decision-making and reporting. However, this wealth of choices can make selecting the right tool a daunting task.

BI and data analytics have transformative potential for organisations that make this important investment. These tools assist organisations to gain valuable and actionable insights from data, improve accuracy and timeliness of reporting, identify new opportunities and leads, mitigate risks, and free up time and effort invested in repetitive and manual data preparation and reporting processes.

Key Considerations When Choosing a BI Tool

• Compatibility: Ensure the tool integrates seamlessly with your existing systems, data sources, and workflows. Compatibility avoids costly disruptions and simplifies implementation.
• Ease of Use: The tool should be easy to use and easy to learn. A tool that is easy to use and to learn will encourages adoption across the organization as it will make things easy. It is important to evaluate the capacity of your teams to learn and/or operate the new tool.
• Scalability: Choose a tool that can grow with your organization, accommodating increasing data volumes and evolving business needs without requiring frequent upgrades.
• Cost Effectiveness: Evaluate both upfront and long-term costs, including licensing, training, and maintenance. A cost-effective solution provides value without compromising functionality.

By considering these factors, organizations can select a BI tool that aligns with their strategic goals and operational requirements, ensuring they harness the full potential of their data to drive success.

Have questions or comments about choosing the right BI tool? We would love to hear from you.

In preparing financial and management reports, finance and accounting professionals often spend considerable time and effort fetching data from multiple sources and systems, cleaning and transforming the data, combining, "linking" tables, aggregating and computations. The process by its nature is also prone to error and inconsistencies.

Until a few years ago, automating routine data transformation tasks was a preserve of a few who had a programming background (including macros and visual basic) or those had easy access to in house data analysts and programmers.

The good news is that automation is now within the reach of almost everyone through BI tools which includes Excel, accessible to almost everyone who uses Office. The licensing and implementation costs are also in my view no longer so prohibitive.

You will be pleased to know that Power Query, which is amazing engine behind Power BI ETL is also available in excel on the data tab. I strongly believe that every finance and accounting professional should be conversant with basic data manipulation, extraction and transformation.

Why Data Literacy Matters

Data literacy allows one to know which tools are available, what are the best tools for your organization and set up, and to employ the best tools for the job. If you are not data literate, you may be lagging behind and missing many opportunities to make informed and better decision for your business. You may also be incurring costs buy using processes that are slow and inefficient, let alone prone to errors.

From as little as 30 minutes a day, you can acquire the much-needed skills to be confident to navigate the data world. It is a rewarding journey.

However, as with any learning journey, discipline and commitment are necessary. You will short-change yourself if you take some random lessons and think you have it. There are no short cuts to success.

Power Query in excel is a good place to start your data transformation journey and for most us it is accessible at no additional cost.

A key difference between a spreadsheet and a business intelligence tool is that with a spreadsheet you import data into a file whereas with a business intelligence tool, you can "connect" to a data source.

There are many advantages to connecting to a data source (using Power Query) as opposed to importing data into a spreadsheet:

• ⚡ Importing data is a repetitive manual process and therefore inefficient.
• ⚡ You only need to connect to a data source once, and thereafter you can access updated data by "hitting" a refresh button. Paths, username and credentials are saved with the data connection wizard and do not need to be re-entered to access the data source unless if the access details are changed.
• ⚡ Imported data requires effort to clean and transform as the data is an unstructured format.
• ⚡ Connecting to data gives access to the underlying tables which often contain structured and clean data, and therefore much easier to work with.
• ⚡ When you connect to a data source, you are able to transform and enrich data before "loading" the data into a data model.
• ⚡ With importing data, it is the reverse, you have to import the entire data set before you can transform and enrich the data.
• ⚡ For this reason, a data model connected to a data source is much faster and more stable compared to a spreadsheet.
• ⚡ Importing data into a spreadsheet has limitations on the number of records that can be imported into a single sheet (1m to my knowledge)
• ⚡ Connecting to a data source allows you to work with an unlimited number of records (I have worked with files with more than 100 million records).
• ⚡ Power Query comes with many connection wizards which makes connecting to many different data sources an easy task, especially for those who don't have a background or training in IT.
• ⚡ Other advantages of connecting to a data source include being able to import and combine files from a folder and to "pick" new files as they are updated.

If you are still doing things the old way, it may be time to switch over to the new way (Power Query).

Having successfully migrated the preparation of management accounts from Microsoft excel based templates and financial statements software to Microsoft Power Bi, the benefits that I have realised include:

• Saves time and effort in preparation – routine & complicated steps can easily be coded and then reused on future reports by simply refreshing the reports, reducing preparation time from days to a few minutes.
• Reports are generated in a consistent manner and therefore more accurate, and less prone to errors and omissions.
• Single source of truth as data is extracted from the same source and using the same parameters.
• We have configured different reports for different audiences by using different "views" of the same database. Switching between views and reporting dimensions requires minimum additional effort.
• Reports are interactive and users are able to customise the reports through self-service options.
• Users can drill down to underlying details and transactions from one portal as reports are generated from detailed ledgers and not from the TB. In our previous reporting process, we had to jump from one system to another, to "piece" together explanations for variances.
• Improved sharing and collaboration. Reports are published on the web and can be viewed and downloaded any time.
• We have started to "push" updated reports automatically to end users at scheduled intervals.
• Access to AI and more visualisation tools that offer more insights into our business and our data.

For a minimum investment, it is now possible to transform manual reporting processes.

Roadmap and Checklist for Finance Process Automation

Finance process automation can transform your operations, but success requires a structured approach. Too often, organizations jump straight to selecting technology without understanding their current state or clearly defining the problem they're trying to solve. This roadmap provides a practical checklist to guide your automation journey from assessment to successful implementation.

After years of implementing reporting, finance, and enterprise solutions, I'd like to share some of the lessons learned that have consistently been critical to successful finance and business transformation.

Most BI initiatives — as well as new system implementations and major upgrades — don't fail because of technology inadequacies. Large-scale projects fail when the design and implementation approach overlooks user needs and fails to get the basics right.

Organizations invest heavily in BI platforms, new systems, and system upgrades, yet adoption often remains low. Solutions look impressive, architectures are complex, and capabilities are advanced — but users quietly revert to spreadsheets, workarounds, or legacy tools.

The problem isn't the technology. It's the approach.

Successful rollouts are about having the right technology that solves both user and organizational needs, and tools that genuinely make life easier because they work reliably and users understand how to use them.

Here is what has worked for me.

✅ Be Clear About the Problem You Are Solving

Before building or deploying a new system, you need absolute clarity on a few fundamentals:

• What problem are we actually trying to solve?
• Is there agreement across the organisation on what that problem is?
• What does success look like, and how will it be measured?

An enterprise-wide view is critical. In some ICT-led projects — which ideally should not be the case — what ICT identifies as a problem may not be a problem for Finance, HR, or the broader business. Likewise, an issue experienced by a support function may not exist for end users at all.

When there is no shared understanding of the problem, systems are designed to address isolated pain points rather than real business needs. The result is predictable: solutions that are technically sound but poorly adopted.

If users don't understand why a new system or upgrade exists — and how it helps them personally — adoption will always be an uphill battle.

✅ Make It Easier, Not Harder: Involve Users From Day One

If a new system or upgraded solution is harder to use or access than what it replaces, users will default to what they know — and that's entirely rational.

Adoption improves dramatically when:

• End users help shape requirements
• Designs and workflows are validated early
• Prototypes or pilots are tested before full rollout

Building in isolation almost guarantees solving the wrong problems or introducing complexity that adds no real value.

✅ Get the Basics Right: Data Accuracy, UAT, and Sign-Off

Nothing destroys confidence faster than inaccurate data or broken processes.

A strong User Acceptance Testing (UAT) process ensures:

• Data and outputs are accurate and complete
• Business logic aligns with how teams actually operate
• Users understand how to interpret and use the system correctly

Formal business sign-off creates accountability and trust — while also serving as practical, hands-on training.

✅ Adequate, Timely Support and Training

First impressions matter.

If users struggle with access, performance, or understanding a new system in the early days, they will immediately revert to familiar tools and processes.

Successful implementations include:

• Clear, well-communicated support and escalation channels
• Timely resolution of user queries
• Positive feedback and visible success stories from early users, with pilot participants becoming ambassadors for the new tool or upgrade

When users know help is available — and that issues will be resolved promptly — confidence grows quickly. Without this, even well-designed solutions lose credibility.

📈 Monitor Tool Adoption and Usage Feedback

Usage metrics and user feedback reveal the reality of adoption:

• What features are actually being used?
• Where are users struggling or disengaging?
• Which capabilities deliver real value — and which do not?

Monitoring adoption allows teams to remove friction, refine functionality, and provide targeted training where it's needed most. Solutions improve when feedback is acted on, and users can see that their input drives real change.

✅ Adopt a Phased, Pilot-First Approach

Adoption should be earned, not enforced.

Users are drawn to systems that clearly make their work easier and decisions better. Rushing large-scale deployments creates resistance, damages trust, and often introduces more problems than it solves.

A pilot-first approach:

• Proves value early
• Surfaces real-world issues
• Incorporates feedback before scale
• Builds confidence organically

Technology should work with the business and its people — not against them.

🔍 Switch Off the Old Tool at the Right Time

Once the new system or upgrade is stable, trusted, and users are trained, make a clear decision: retire the old solution.

Running parallel systems creates confusion, inconsistent outputs, and slows adoption.

Have questions or comments about ensuring successful BI adoption? We'd love to hear from you.

Implementation of a BI project, like any other new technology platform or tool, has its risks and challenges which in my view include:

To ensure success, organisations should consider the following:

What has been your experience?

AI is here. Everyone is talking about it, and it is unlikely to go away.

As with any major technological shift, AI brings a mix of excitement, expectation, and hype. The challenge for finance leaders is to filter out the noise and focus on what will genuinely create value for their teams and organizations—both now and in the near future.

Ignoring AI altogether is not a low-risk option. It can mean missed opportunities to operate more efficiently, weaker decision support, and erosion of competitive advantage over time. The question is not whether to engage with AI, but how to do so in a way that is measured, pragmatic, and aligned with business priorities.

Before embarking on large-scale AI initiatives or making significant investments, it is critical that organizations and finance teams prepare for AI implementation. Preparation increases the likelihood of success and helps avoid costly investments in technologies that are not fit for purpose.

For me, the critical factor is awareness. Understanding what AI technologies exist, how they are being applied in practice, and what lessons and experiences are emerging from early adopters. This awareness enables leaders to make informed, timely decisions that are appropriate for their specific context, risk profile, and operating environment.

First Steps May Include:

I hope you find this roadmap useful. I'd be interested to hear about your experience with AI in Finance so far.

Microsoft released the Power BI MCP server recently. I was able to install the server easily in GitHub. I hit a few glitches in trying to connect GitHub Copilot to my open desktop file, but after several attempts and troubleshooting I am connected.... :)

I have been using AI for refining and creating measures in Power BI for a while which has been quite handy in troubleshooting and accelerating development and improvements to reports. I am looking forward to Microsoft MCP as it offers Agentic mode - ability to not only suggest changes but implement them on the Power BI file.

Of course this comes with risks, but also huge productivity opportunities if the AI understands my prompts and has the correct context.

Have questions or comments about using Agentic AI with Power BI? We'd love to hear from you.

© CMN Consulting 2025 | cmnconsulting.co.za

I had the privilege of delivering the Keynote Address at the SA Student Finance Forum on the topic: Using AI to Enhance Student Debt Reporting and Management.

In my keynote, I highlighted how AI basics are important, but more critically, how Generative AI provides a powerful bridge for finance staff without coding or programming expertise to discover, explore, and interact with data.

Our institutions of higher learning sit on vast amounts of data. Yet, many are not extracting the insights due to siloed operations or limited skills in data analysis. This presents both a challenge and an opportunity.

I challenged delegates to embrace AI as a learning journey so that all staff at all levels can meaningfully participate in the AI and data economy. By building literacy in AI and data, we can unlock new approaches to student debt reporting, management, and sustainable solutions for the future.

A heartfelt thank you to SASFF for the platform and for convening a successful conference.

Have questions or comments about using AI for student debt management? We'd love to hear from you.

© CMN Consulting 2025 | cmnconsulting.co.za

There is a lot of interest and excitement around the opportunities AI has to offer, and there's no doubt that AI is going to have a significant impact on how we do things. Any new technology brings both opportunities (such as gaining a competitive advantage) and risks (like not achieving the expected rate of return). At the very least, finance leaders should understand what AI is and the opportunities it presents for current and future business processes.

AI has been around for a while, with past use cases including search engines, spell checks, and navigation tools. However, recent advancements in AI have expanded its capabilities beyond these traditional applications. These improvements have also made AI more affordable and accessible for everyday use by a larger portion of society (as long as one has internet connectivity and a smart device).

A good friend of mine challenged me to make greater use of AI—specifically, ChatGPT—and I am excited about what it has to offer. For me, AI functions like a core worker or expert that I can call upon anytime.

My use cases, so far, include:

One of the greatest advantages of AI is its speed and ability to provide real-time feedback, which significantly boosts productivity.

There are plenty of learning materials available on how to use AI tools. While I haven't enrolled in a complete course yet, I find that learning AI is like learning a language—you get better with time and practice, and there are multiple pathways to achieve the same result.