When building measures in Power BI using DAX, two commonly used aggregation functions are COUNT and COUNTA. While they sound similar, they serve different purposes and choosing the right one can prevent inaccurate results in your reports.
COUNT: Counting Numeric Values Only
The COUNT function counts the number of non-blank numeric values in a column.
DAX syntax:
COUNT ( Table[Column] )
Key characteristics of COUNT”:
Works only on numeric columns
Ignores blanks
Ignores text values entirely
When to use COUNT:
You want to count numeric entries such as:
Number of transactions
Number of invoices
Number of scores, quantities, or measurements
The column is guaranteed to contain numeric data
Example: If Sales[OrderAmount] contains numbers and blanks, COUNT(Sales[OrderAmount]) returns the number of rows with a valid numeric amount.
COUNTA: Counting Any Non-Blank Values
The COUNTA function counts the number of non-blank values of any data type, including text, numbers, dates, and Boolean values.
DAX syntax:
COUNTA ( Table[Column] )
Key characteristics of “COUNTA”:
Works on any column type
Counts text, numbers, dates, and TRUE/FALSE
Ignores blanks only
When to use COUNTA:
You want to count:
Rows where a column has any value
Text-based identifiers (e.g., Order IDs, Customer Names)
Dates or status fields
You are effectively counting populated rows
Example: If Customers[CustomerName] is a text column, COUNTA(Customers[CustomerName]) returns the number of customers with a non-blank name.
COUNT vs. COUNTA: Quick Comparison
Function
Counts
Ignores
Typical Use Case
COUNT
Numeric values only
Blanks and text
Counting numeric facts
COUNTA
Any non-blank value
Blanks only
Counting populated rows
Common Pitfall to Avoid
Using COUNTA on a numeric column can produce misleading results if the column contains zeros or unexpected values. Remember:
Zero (0) is counted by both COUNT and COUNTA
Blank is counted by neither
If you are specifically interested in numeric measurements, COUNT is usually the safer and clearer choice.
In Summary
Use COUNT when the column represents numeric data and you want to count valid numbers.
Use COUNTA when you want to count rows where something exists, regardless of data type.
Understanding this distinction ensures your DAX measures remain accurate, meaningful, and easy to interpret.
Excel Power Query is a powerful, no-code/low-code tool that allows you to combine and transform data from multiple sources in a repeatable and refreshable way. One common use case is merging two Excel files or worksheets based on multiple matching columns, similar to a SQL join. Power Query is a major part of Power BI, but it can be used in Excel.
When to Use Power Query for Merging
Power Query is ideal when:
You receive recurring Excel files with the same structure
You need a reliable, refreshable merge process
You want to avoid complex formulas like VLOOKUP or XLOOKUP across many columns
Step-by-Step Overview
1. Load Both Data Sources into Power Query
Open Excel and go to Data → Get Data
Choose From Workbook (for separate files) or From Table/Range (for sheets in the same file)
Tip: Ensure the columns you plan to merge on have the same data types (e.g., text vs. number).
Load each dataset into Power Query as a separate query
2. Start the Merge Operation
In Power Query, select the primary table
Go to Query → Merge Queries
Choose the secondary table from the dropdown
3. Select Multiple Matching Columns
Click the first matching column in the primary table
Hold Ctrl (or Cmd on Mac) and select additional matching columns
Repeat the same column selections in the secondary table, in the same order
For example, if you needed to perform the merge on CustomerID, OrderDate, and Region, you would click Customer ID, then hold the Ctrl key and click OrderDate, then (while still holding down the Ctrl key) click Region.
Power Query treats this as a composite key, and all selected columns must match for rows from both tables to merge.
4. Choose the Join Type
Select the appropriate join kind:
Left Outer – Keep all rows from the first table (most common) and brings in the values for the matching rows from the second table
Inner – Keep only matching rows from both tables
Full Outer – Keep all rows from both tables, merging the table where there is a match and having just the values from the respective tables when there is no match
Click OK to complete the merge.
5. Expand the Merged Data
A new column appears containing nested tables
Click the expand icon to select which columns to bring in
Remove unnecessary columns to keep the dataset clean
6. Load and Refresh
Click Close & Load
The merged dataset is now available in Excel
When source files change, simply click Refresh to update everything automatically
Key Benefits
Handles multi-column joins cleanly and reliably
Eliminates fragile lookup formulas
Fully refreshable and auditable
Scales well as data volume grows
In Summary
Using Power Query to merge Excel data on multiple columns brings database-style joins into Excel, making your workflows more robust, maintainable, and professional. Once set up, it saves time and reduces errors—especially for recurring reporting and analytics tasks.
Few industries have embraced AI as broadly—or as aggressively—as financial services. Banks, insurers, investment firms, and fintechs operate in data-rich, highly regulated environments where speed, accuracy, and trust matter. AI is increasingly the engine that helps them balance all three.
How AI Is Being Used Today
AI shows up across nearly every function in financial services:
Fraud Detection & Risk Monitoring Machine learning models analyze transactions in real time to identify suspicious patterns, often catching fraud faster and more accurately than rule-based systems. PayPal utilizes AI-powered systems to detect fraud by comparing transactions with historical patterns, reducing financial losses. This is extremely critical in this time of rampant fraud. Financial Institutions also use AI to analyze real-time working capital and historical data to forecast financial performance and predict trends with greater accuracy.
Credit Scoring & Underwriting AI evaluates borrower risk using far more signals than traditional credit scores, including transaction behavior and alternative data (where regulations allow). Upstart, an AI-based lending platform, uses non-traditional data to assess creditworthiness, approving loans quickly for customers who might otherwise be denied by conventional models.
Customer Service & Virtual Assistants Chatbots and voice assistants handle balance inquiries, dispute tracking, loan status updates, and more—freeing human agents for complex cases. Bank of America’s Erica, a virtual assistant, assists customers with account information, bill payments, and personalized financial advice through chat or voice.
Algorithmic & Quantitative Trading AI models analyze market signals, news sentiment, and historical trends to inform trading strategies and portfolio optimization. Goldman Sachs uses generative AI to optimize trading strategies and forecast market trends, gaining a competitive edge in dynamic markets.
Compliance & AML (Anti–Money Laundering) AI tools assist in ensuring compliance with regulatory requirements by automating the monitoring transactions and reporting. This reduces the risk if non-compliance and associated penalties. HSBC utilizes AI to process compliance documents efficiently, ensuring adherence to evolving regulations and minimizing manual errors. AI also helps identify money laundering patterns, reduce false positives, and prioritize investigations.
Personalized Financial Advice Robo-advisors and recommendation engines tailor savings, investment, and retirement strategies to individual customers. Wells Fargo’s predictive banking feature provides personalized prompts about future financial activities leading to improved user engagement.
Tools, Technologies, and Forms of AI
Financial services organizations typically use a layered AI stack:
Machine Learning & Deep Learning Core to fraud detection, risk modeling, and forecasting.
Natural Language Processing (NLP) Used to analyze customer communications, earnings reports, regulatory filings, and market news.
Generative AI & Large Language Models (LLMs) Emerging use cases include advisor copilots, automated report generation, customer communication drafting, and internal knowledge search.
AI Platforms & Infrastructure Cloud platforms like AWS, Azure, and GCP provide scalable ML services, while many firms also invest in proprietary, on-prem models for sensitive workloads.
Decision Intelligence & Optimization Models AI combined with rules engines to support pricing, underwriting, and capital allocation decisions.
Blockchain and AI Integration Blockchain and AI integration will redefine how financial transactions are managed, enhancing security, transparency, and efficiency. Blockchain technology ensures trust and accountability, while AI improves transaction validation and fraud detection. Together, these technologies will streamline cross-border payments, smart contracts, and digital identities, creating a more secure and efficient financial ecosystem.
Benefits Financial Institutions Are Seeing
Organizations that have successfully deployed AI are seeing tangible gains:
Reduced Fraud Losses and faster detection
Lower Operating Costs through automation of high-volume tasks and improved efficiencies
Improved Customer Experience with faster responses and personalization
Better Risk Management via more dynamic and data-driven models
Increased Revenue through smarter cross-sell, upsell, and pricing strategies
In short, AI helps firms move from reactive decision-making to proactive, predictive operations.
Pitfalls and Challenges
Despite the promise, AI in financial services comes with real risks:
Bias and Fairness Concerns AI models can unintentionally reinforce historical bias in lending or underwriting decisions, creating regulatory and ethical challenges.
Model Explainability Regulators and auditors often require clear explanations for decisions—something black-box models struggle to provide.
Data Quality and Silos Poor data governance leads to unreliable models and failed AI initiatives.
Regulatory Risk Financial institutions must ensure AI usage aligns with evolving regulations across regions.
Overhyped Projects Some AI initiatives fail because they chase cutting-edge technology without clear business ownership or measurable outcomes.
Where AI Is Headed in Financial Services
Looking ahead, several trends are emerging:
AI as a Copilot, Not a Replacement Advisors, underwriters, and analysts will increasingly work alongside AI systems that augment—not replace—human judgment.
More Explainable and Governed AI Expect increased focus on transparency, auditability, and model governance.
Real-Time, Embedded Intelligence AI will be embedded directly into workflows—credit decisions, claims processing, and trade execution—rather than sitting in separate tools.
Greater Use of Generative AI From personalized financial guidance to internal knowledge assistants, GenAI will reshape how employees and customers interact with financial systems.
How Financial Services Companies Can Gain an Advantage
To stay ahead in this fast-changing landscape, organizations should:
Start with High-Impact Use Cases Focus on areas like fraud, customer experience, or risk where ROI is clear.
Invest in Data Foundations Clean, well-governed data is more valuable than the most advanced model.
Build AI Governance Early Fairness, explainability, and compliance should be part of design—not afterthoughts.
Upskill the Workforce AI-literate business leaders and domain experts are just as important as data scientists.
Blend Human and Machine Intelligence The most successful systems pair AI recommendations with human oversight.
Final Thoughts
AI is no longer experimental in financial services—it’s essential infrastructure. Firms that treat AI as a strategic capability, grounded in strong data practices and responsible governance, will be best positioned to innovate, compete, and earn trust in an increasingly intelligent financial ecosystem.
Are you using AI in the financial services industry? Share how and what you have learned from your journey.
This article is a part of an “AI in …” series that shares information about AI in various industries and business functions. Be on the lookout for future (and past) articles in the series.
The location of your source files has changed, and now you need to update your Power BI report to use the new location. To update the directory or location of your source file, in Power BI Desktop, click Transform Data -> Data Source Settings
Then click on the entry that corresponds to the path you need to update.
Update or entirely change the path and click ok. Apply your changes.
It becomes a little more complicated when you are changing a local folder to a SharePoint location, which we will cover in another post, but for changing location of single files, it’s that simple.
You may run into this error when loading Power BI:
"load was cancelled by error in loading a previous table"
If you do get this error, keep scrolling down to see what the “inducing” error is. This message is an indication that there was an error previous to getting to the current table in the process. The real, initial error will be more descriptive. Start with resolving that error(s), and then this one will go away.
I was getting this error message when I attempted to refresh a Power BI application:
"Column 'Date' in table 'Date Dim' contains blank values and this is not allowed for columns on the one-side of a many-to-one relationship or for columns that are used as the primary key of a table"
However, despite what the message indicated, I double-checked and confirmed that I did not have any blank values in the ‘Date Dim’ table.
It turns out that you may also get this error (although incorrectly worded in my opinion) if the blanks are in the joining table. In my case, I had blanks in a ‘Snapshot Date’ column in the fact table that was joined to the ‘Date Dim’ table. Once these blanks were filled, the refresh ran without error.
One thing to look out for in these cases (since this is what happened in my case), if your source is Excel, undo all filters to make sure that you do not have any rows being filtered out when checking for blanks values across your columns, because this could potentially inadvertently hide the rows with the blank values and cause you to miss them.
I hope you found this helpful.
Information and resources for the data professionals' community
The Data Community 