How to Make Your Excel Data Work Well with Power BI Q&A

Power BI Q&A is the feature that lets someone type a plain-English question, "what were sales in Queensland last quarter", and get a chart back without touching a single filter. When it works, it is the closest thing to magic that most business users will ever see in a reporting tool. When it does not work, it is the fastest way to make people distrust the whole dashboard. The difference between those two outcomes has almost nothing to do with Q&A itself. It is about how you prepared the data underneath it.

That is the part Microsoft's documentation on preparing data for Q&A walks through, and it is worth reading. What I want to add is the consulting view: which of these steps actually move the needle, which ones people skip and regret, and how we set up Excel-sourced models so Q&A gives answers people believe.

Why this matters more than it looks

Here is the thing about natural-language querying. The moment you put a "ask a question" box in front of a business user, you are making a promise. You are saying: ask me anything and I will answer it correctly. If someone types "revenue by region" and gets nothing, or gets the wrong number, they do not conclude that they phrased it poorly. They conclude the dashboard is broken and go back to asking the analyst by email. Trust is expensive to build and cheap to lose, and a bad Q&A experience burns it fast.

The reason Q&A struggles is almost always that the data was never structured for a machine to interpret in plain language. Excel is built for humans. We happily read a tab called "Sheet3" with a column headed "Q3 (adj)" and a merged cell explaining what "adj" means. Q&A cannot. It reads names literally, and if your names are cryptic, abbreviated, or ambiguous, the feature has no chance. So the work is mostly about translating a spreadsheet built for people into a model a language engine can reason about.

Name things like a person would say them

This is the single highest-value thing you can do, and most people skip it. Q&A matches the words a user types against the names of your tables, columns, and measures. If your column is called cust_rev_amt, nobody is going to type that, and Q&A is not going to guess it from "customer revenue".

Rename tables and columns to the words people actually use out loud. Sales, Customer, Region, Order Date, Revenue. If your business calls something a "client" rather than a "customer", name it "Client", because that is what people will type. This sounds obvious and it is astonishing how often it gets ignored, because the person who built the model named the columns for themselves, months ago, in a hurry.

Get rid of the Excel residue too. Columns like Column1, tabs like Data (2), header rows sitting three rows down under a title and a blank line. Q&A takes your names at face value, so a table called Sheet1 is a table Q&A has nothing useful to say about. Clean this up and you have done more for the Q&A experience than any amount of clever configuration.

Getting a model into this shape is a chunk of the value we deliver on reporting work. It is the unglamorous part our Power BI consultants do early, because a well-named model is easier for everyone, not just Q&A.

Relationships, and why one flat table is not enough

A lot of Excel-born models are a single wide table with everything crammed in, because that is how the spreadsheet was. Q&A can work with that, but it comes alive when the model has proper relationships between tables.

When you have a Sales table related to a Customer table related to a Region table, Q&A can follow those links. Someone asks "sales by region" and it knows how to get from a sale to a region even though region does not live in the sales table. Without the relationship, that question has no answer. So splitting a flat export into sensible related tables, a proper star schema, is not just tidy modelling for its own sake. It directly expands the set of questions Q&A can answer.

This is also where data types earn their keep. If your dates are stored as text, Q&A cannot reason about "last quarter" or "this year", because as far as it knows those are just strings. Make sure dates are real dates, numbers are real numbers, and you have a date table marked as such. Time-based questions are some of the most common things people ask, and they only work if the underlying types support them.

Synonyms are the feature nobody uses and everybody needs

Here is my favourite underused part of the whole thing. Power BI lets you define synonyms, alternative words that map to a table, column, or measure. Your column is called Revenue, but people in your business say "sales", "takings", "income", "turnover". Add those as synonyms and Q&A understands all of them.

This is the setting that separates a Q&A experience that feels clever from one that feels stupid. Every business has its own vocabulary. A logistics firm says "consignments", a medical practice says "patients", a real estate group says "listings". If you only feed Q&A the formal column name, half the questions your actual users ask will miss. Spend an afternoon sitting with the people who will use the report, write down the words they naturally use, and load those in as synonyms. It is the highest-return hour you will spend on the whole model.

There is a related tool worth knowing about, a linguistic modelling schema you can edit to teach Q&A more about how your terms relate and how phrases map to your data. It goes deeper than synonyms and it is genuinely powerful, though I will be honest, it is fiddly and most teams get 90% of the benefit from good names and a solid synonym list. Reach for the deeper tooling when you have a high-visibility report where the last 10% matters.

What to watch out for

Let me be straight about the rough edges, because the demos always look flawless and real life is bumpier.

Q&A is confident even when it is wrong. It will happily interpret an ambiguous question and hand back an answer that looks authoritative, and the user has no easy way to tell it misread them. If you have two measures that could both plausibly match "profit", Q&A picks one, and it might not be the one they meant. This is why clear, unambiguous naming is not a nicety. An ambiguous model produces confidently wrong answers, which is worse than no answer at all. Test it by having someone who did not build the model ask it the questions they actually care about, and watch where it stumbles.

It also does not know what it does not have. If someone asks about a metric that is not in the model, they get an unhelpful non-answer, and they do not know whether that is because the data is missing or because they phrased it wrong. Managing expectations about what the model covers is part of rolling this out well. A short note on the report saying what it can answer goes a long way.

And it is only as current as your refresh. Q&A answers from the model, so if the underlying Excel refresh has quietly failed, Q&A will cheerfully answer questions with stale data and give no hint that anything is off. The natural-language layer is only ever as trustworthy as the plumbing beneath it, which is a good reminder that getting the data foundation right is the thing everything else depends on.

My honest take

Q&A is one of those features that is genuinely useful and consistently oversold. In a vendor demo it looks like you can throw a messy spreadsheet at Power BI and start asking it anything. In reality, the quality of the answers is a direct reflection of the work you put into the model. Good names, real relationships, correct data types, and a solid synonym list, and it feels genuinely helpful. Skip that and it feels like a party trick that breaks the moment a real user touches it.

For Excel-sourced data specifically, the gap between "spreadsheet as a human reads it" and "model as Q&A reads it" is the whole job. That translation work is not glamorous and it is not quick, but it is what makes the difference between a feature people actually use and one they try once and abandon. If you are rolling this out to a broad audience who will judge the dashboard by whether Q&A gives them sensible answers, put the preparation in. It pays off every single time someone types a question and gets the right chart back.

If you want a hand getting a model into that shape, or you have a Q&A experience that is giving people odd answers and you cannot work out why, that is squarely the kind of thing we do. Have a look at our data and analytics services or get in touch and tell us what you are trying to build.