Power BI Dashboards: Two Steps From Excel to Live Report - PC Webinars blog post

Power BI Dashboards: Two Steps From Excel to Live Report

Most people do not arrive at Power BI dashboards because they wanted a new piece of software. They arrive because the Excel file finally got too big. The pivot table takes nine seconds to refresh, the chart tab has to be rebuilt every month, and somebody always asks for the numbers sliced a way the workbook was not built to slice.

I have watched this happen in class for years. Someone opens Power BI Desktop, points Get Data at the same spreadsheet, drags a few fields onto the canvas, and ends up with something that looks like a slower Excel chart. Then they decide Power BI is overrated and go back to the workbook.

The gap is almost never talent. It is two habits nobody tells you about on day one.

Step One: Click Transform Data, Not Load

When you choose Home > Get Data > Excel workbook and pick your file, the Navigator window appears with two buttons at the bottom. Nearly everyone clicks Load. Click Transform Data instead. That opens Power Query, and Power Query is where the monthly rebuild stops being your job.

Once the Power Query Editor is open, do these four things to the table:

  • Use First Row as Headers on the Home tab, if your column names came in as Column1, Column2, Column3.
  • Delete the junk rows. Home > Remove Rows > Remove Top Rows handles the title and blank rows that sit above real data in almost every exported report.
  • Set each column type by clicking the little icon to the left of the column name. Dates as Date, amounts as Decimal Number. Guessing here is where most refresh errors are born.
  • If your region or category sits across columns instead of down rows, select the columns you want to fold up, then Transform > Unpivot Columns. One click turns a crosstab into the tall skinny table every chart in Power BI actually wants.

Now click Close & Apply. Every step you just did is recorded in the Applied Steps panel on the right. Next month, when the new file lands in the same folder with the same shape, you hit Refresh and all four steps run again in order. That is the whole payoff: you cleaned the data once instead of twelve times.

The gotcha: Power Query hardcodes the file path. If you built the query against a file sitting on your desktop and then email the report to a colleague, their refresh fails. Point the query at a shared network folder or a SharePoint location before you hand the file to anyone, and change it later under Transform data > Data source settings.

Step Two: Build Power BI Dashboards on Measures, Not Dragged Columns

When you drag a numeric column like Amount onto a visual, Power BI quietly guesses that you want to sum it. That is an implicit aggregation, and it works right up until you need the same number somewhere else, formatted the same way, or changed in one place.

Instead, create a measure. Right-click your table in the Data pane, choose New measure, and type:

Total Sales = SUM(Sales[Amount])

Press Enter, then on the Measure tools ribbon set the format to Currency with zero decimals. Now drag Total Sales onto a card, a bar chart, and a table. All three show the same number, formatted identically, and when the business decides that sales should exclude cancelled orders, you edit one line of DAX and every visual on every page updates.

Once you have one measure, the next ones get easy:

  • Order Count = COUNTROWS(Sales)
  • Average Order = DIVIDE([Total Sales], [Order Count])

Use DIVIDE rather than the slash. DIVIDE returns a blank instead of an error when the bottom number is zero, which keeps a single empty month from blanking out an entire visual.

The gotcha: do not confuse a measure with a calculated column. A calculated column is computed once per row and stored in the file, which bloats it. A measure is computed on the fly for whatever the user has filtered. If the answer depends on what is selected on the page, you want a measure. Nine times out of ten in a dashboard, you want a measure.

Do those two things and the report stops being a monthly chore: the data cleans itself on refresh, and the numbers come from one definition you control.

Plenty more on the calendar

I am running a live session called Microsoft Power BI for Beginners and Excel Users on Wednesday, November 18. We go through the whole path in one sitting: importing an Excel file, cleaning it in Power Query, building the first set of Power BI dashboards, writing your first measures, and publishing the result so other people can open it. It is aimed at people who already know Excel well and want those skills to carry over.

The full current schedule lives on the front page of PCWebinars.com, with new dates going up as they are confirmed. Register for this one, or browse the rest and find the session that matches what is on your desk right now.

About your trainer

I am Tom Fragale. I have been a computer trainer and consultant for more than 30 years, and I have 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, including 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.

If this was useful, come see the rest of what is coming up at PCWebinars.com.

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *