Power BI Data Model: Two Beginner Steps That Save Hours

Almost every Power BI class I teach has the same moment in it. Somebody gets a chart on the screen inside of fifteen minutes, feels great about it, and then the numbers come out wrong. A customer counted twice. A total that does not match what accounting sent over. A refresh that quietly fails on Monday morning. Nine times out of ten the visuals are fine — the trouble is underneath, in the Power BI data model.

That is the part people skip, and I understand why. Power BI makes it very easy to point at a spreadsheet, hit Load, and start dragging fields onto the canvas. It works beautifully right up until the data changes.

So here are the two steps I ask people to take before they build a single chart. Neither one takes long, and between them they head off most of the rework I see in class.

Step 1: Shape the Data in Power Query Before It Reaches Your Power BI Data Model

When you click Get Data in Power BI, you are offered a Load button and a Transform Data button. Almost everybody clicks Load. Click Transform Data instead. That opens Power Query, and every cleanup step you take in there is recorded and re-run automatically each time the report refreshes. Clean it once, it stays clean forever.

Here is the short list I run through on nearly every source file before I let it anywhere near the model:

• Home > Remove Rows > Remove Top Rows to strip off the title banner somebody put above the data, then Home > Use First Row as Headers.
• Click each column heading and set the data type on the Transform tab. Dates that arrived as text are behind more broken reports than any other single thing.
• Transform > Format > Trim on your text columns. A trailing space is what turns “Acme Corp ” and “Acme Corp” into two different customers.
• If your spreadsheet has months running across the top, select the columns you want to keep as labels and choose Transform > Unpivot Other Columns. That turns a wide grid into the tall, narrow list Power BI actually wants. It takes about five seconds and it saves hours.

Keep an eye on the Applied Steps pane on the right as you work. That list is your recipe. If you get a step wrong, delete it and do it again — you are never stuck.

The gotcha: Power Query bakes the full file path into that very first step. Point it at something sitting in your Downloads folder and the report will refresh perfectly on your machine and fail for everyone else. Move the source file to its permanent home — a SharePoint or OneDrive folder the whole team can reach — before you connect to it, not after.

Step 2: Build a Real Power BI Data Model Instead of One Giant Table

The habit we all bring over from Excel is to VLOOKUP everything into one enormous wide sheet. Power BI does not want that. It wants your transactions in one table and your descriptive lists — customers, products, dates — sitting in their own separate tables, joined together.

Go to Model view, the third icon down the left edge. Drag the CustomerID field from your sales table and drop it onto CustomerID in your customer table. That is the entire operation. Now a region slicer built on the customer table filters your sales automatically, with no lookup formula anywhere in the file.

Then add a proper date table, because that is what makes any time comparison work. On the Table tools ribbon pick New table and enter:

Calendar = CALENDAR(DATE(2023,1,1), DATE(2027,12,31))

Join its Date column to the date column in your sales table, then select the Calendar table and click Mark as date table. From that point on, a measure like this one behaves itself:

Sales LY = CALCULATE(SUM(Sales[Amount]), SAMEPERIODLASTYEAR(Calendar[Date]))

It returns last year’s figure correctly, and it keeps returning it correctly when somebody slices the report by quarter or by month.

The gotcha: Power BI will happily auto-detect relationships for you, and it sometimes gets them wrong — usually by joining on a column that is not actually unique, or by setting the filter to flow in both directions. Open Model view and look at every line you see. Each one should show a 1 on the lookup-table end and a * on the transaction end, with the arrow pointing from the one toward the many. A relationship with arrows on both ends is very often the reason a total refuses to add up.

Plenty more on the calendar

If you would rather watch all of this built from an empty screen, I am running Mastering Power BI: A Comprehensive Guide for Beginners live on Friday, September 18. We take a raw spreadsheet and walk it the whole way through — Power Query cleanup, relationships, a date table, measures, and a finished dashboard — and I take questions as we go rather than saving them for the end.

The full current schedule is on the front page of PCWebinars.com, and I put new dates up as they are confirmed. Register for this one, or browse the rest and pick whichever session is closest to what is sitting on your desk right now.

About your trainer

I’m Tom Fragale. I’ve been a computer trainer and consultant for more than 30 years, and I’ve taught well over 30,000 people how to get more out of the software they already own — Excel, Access, Power BI, Word, Outlook, and now the AI tools that sit on top of all of it: Claude, ChatGPT, and Microsoft Copilot. My background is applied, not theoretical. Alongside teaching, I build real databases and reporting systems for clients in Excel, Access, and Power BI, so the examples I use in class are the problems I ran into last week, not textbook exercises. I also publish free tutorials on YouTube and longer courses online.

You can see everything I have coming up, and pick up more free material, over at PCWebinars.com.