Skip to content
Tutorials Center
My learning

Topics

Lesson 5 of 56 ยท Welcome to Power BI

Power BI and Excel Together

Learn how Power BI and Excel work together: import workbooks, Analyze in Excel, connect a PivotTable to a semantic model, upload and refresh Excel files, and when to use which.

Almost everyone who learns Power BI already knows Excel, and almost every company has data in Excel files. That is good news: the two tools are designed to work together. Excel is the place for quick analysis, one-off calculations and spreadsheets with hand-entered numbers. Power BI is the place for shared, refreshed, secured reports on larger data. In practice you will move data and ideas between them all the time.

This lesson covers the connections in both directions: loading an Excel file into Power BI, uploading workbooks to the service, Analyze in Excel (pivoting a Power BI model from inside Excel), connecting to published models from Excel, and exporting data. At the end you will know which tool to use for which job.

Power BI and Excel are desktop and cloud programs, so this lesson describes the steps in words. The runnable examples are Python stand-ins built on pandas: they show how a messy worksheet becomes a clean table and what a PivotTable on a model calculates. They are not Excel or Power BI.

Loading an Excel file into Power BI Desktop

On the Home ribbon choose Get data > Excel workbook and pick the file. The Navigator window lists what is inside:

  • Sheets (the whole used range of a worksheet).
  • Tables (Excel tables created with Ctrl+T, shown with a table icon).
  • Named ranges.

Prefer tables or named ranges to whole sheets. A table has a clear header row and a clear boundary, so new rows are picked up automatically when you refresh. A sheet may include titles, notes, blank rows and totals that you then have to remove in Power Query. Click Transform Data to open the Power Query Editor, or Load to load straight into the model. See Connecting to Data for the other sources.

What messy worksheets look like

Spreadsheets made for people are not made for models. Typical problems: a title above the headers, blank rows, merged cells (so only the first row of a group has a value), totals rows in the middle, and numbers stored as text. In Power Query you fix them with Remove Top Rows, Use First Row as Headers, Fill Down, Remove Rows and Change Type, as covered in Cleaning Data in Power Query.

Python Stand-in: a messy sheet becomes a table

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Notice that the sheet's own total (1,500) does not match the sum of the clean rows (1,460): somebody typed it by hand, and the n/a cell is not a number at all, so it became an error value that the sum skips. The totals row is a reason to delete it. Power BI should calculate totals itself from the detail rows, which is what a measure does.

Where the file lives matters for refresh

When you publish a report, the file path is stored with the semantic model. Refresh then depends on where the file is:

File location Refresh in the service
OneDrive for Business or SharePoint (connect with the web URL) Works with no gateway. The service reads the file directly.
Local disk or network share Needs an on-premises data gateway to reach it.
Moved or renamed file Breaks refresh until you update the path in Transform data > Data source settings.

For shared reports, put the Excel file in OneDrive or SharePoint and connect to it with the Web address from File > Copy link (strip any trailing ?web=1). The details of gateways are in Scheduled Refresh and Gateways, and credentials in Data Sources and Credentials.

Uploading a workbook to the Power BI service

You can also give a workbook to the service without Desktop. In a workspace choose New > Upload a file (or Get data > Files) and pick the Excel file. When you add a workbook, the service offers two things:

  • Import Excel data into Power BI. The tables or the data model inside the workbook become a semantic model, which you can use for reports. The workbook itself is not shown.
  • Upload (or connect to) the workbook. The file stays an Excel file that people can open in Excel for the web, with its sheets and charts. This is useful when the workbook is the deliverable, not just a source.

If the workbook is stored on OneDrive and you connect to it, edits you save in Excel flow to the service automatically, usually within an hour. The Power Pivot data model in a workbook can also be brought into Desktop with File > Import > Power Query, Power Pivot, Power View, which turns Excel models and queries into a Power BI file.

Analyze in Excel: Power BI data in a PivotTable

Many analysts want to work with a published model in Excel's PivotTable interface. That is what Analyze in Excel does. In the Power BI service, open a workspace, click the More options (...) of a semantic model or report and choose Analyze in Excel. You get a small connection file that opens in Excel, and a blank PivotTable is already connected to the model. The field list shows the model's tables, columns and measures, with the same names and calculations as in the report.

What this gives you:

  • One definition of truth. Total Sales in the PivotTable is the same DAX measure as in the report, not a new formula.
  • Live data. Use Data > Refresh All in Excel and the PivotTable asks the model again; it is not a copy.
  • Security still applies. Row-level security filters what you see, as in Row-Level Security.
  • Permissions. You need Build permission on the semantic model and a Pro, Premium Per User or Fabric-capable license, and Excel desktop (or Excel for the web where available).

You can also start from Excel: Insert > PivotTable > From Power BI (or Data > Get Data > From Power BI) lists the semantic models you can access and connects a PivotTable to the one you choose. Either way, you get PivotTables, slicers, CUBE functions and Excel charts on top of the shared model.

Try it: what the PivotTable computes

Python Stand-in: a PivotTable over a model with a measure

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The last two lines show why Analyze in Excel is valuable: a normal Excel PivotTable summing a column of percentages would give the "average of the cells", while the model's measure gives the correct weighted margin in every cell and in the grand total, as explained in DAX Basics.

Exporting from Power BI to Excel

In a report, hover over a visual, open More options (...) and choose Export data. You can export the summarized data (what the visual shows) or the underlying data (the detailed rows behind it, if the report author allows it), to Excel (.xlsx) or CSV. Exports have row limits and an administrator can turn them off in the tenant settings, and exported files from a labeled report carry the sensitivity label (see Governance and Deployment). Also, do not use export as a data pipeline: a copy has no refresh, no security and no history of where it came from. If many people export the same report, build them an Analyze in Excel connection instead.

SQL and databases in Excel

Excel has the same Power Query engine as Power BI (Data > Get Data), including connectors for SQL Server and other databases. A query you built in Excel can be copied into Power BI through the Advanced Editor, and the other way around, so skills transfer. The difference is what comes after: Excel loads the result into a sheet or its own data model for one person, while Power BI loads into a model that you publish and refresh for everyone. Writing a SQL statement in the connector's advanced options (the SQL statement box) works the same in both, but beware that custom SQL disables query folding for the following steps, as in Query Folding and Incremental Refresh.

Power BI or Excel?

Situation Better choice
A one-off calculation on a small dataset Excel
Hand-entered budgets, scenarios and what-ifs Excel
A report that is refreshed every day and shared with 50 people Power BI
More than about a million rows or several related tables Power BI (the engine is built for this)
Row-level security Power BI
Pixel-exact printed layouts of a worksheet Excel (or paginated reports)
Letting analysts pivot governed data Analyze in Excel on a Power BI model

They are partners, not rivals: many teams keep inputs in Excel, model and publish in Power BI, and let power users explore with Analyze in Excel.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. In the Navigator, which kind of Excel object is the safest to load?

    Tables and named ranges have clear headers and boundaries, and they grow automatically when rows are added.
  2. 2. Where should an Excel source be stored for scheduled refresh without a gateway?

    Cloud storage can be reached by the service directly. A local file needs an on-premises data gateway.
  3. 3. What does Analyze in Excel provide?

    It creates a connection to the model so Excel users pivot the same data and measures, with security applied.
  4. 4. Which permission is needed on a semantic model to use it in Analyze in Excel?

    Build permission lets a user create new content, such as a PivotTable, on the model.
  5. 5. Why delete a worksheet's own Total row before loading it?

    A total row loaded as data doubles every sum. Measures compute the totals correctly.

Summary

  • Load Excel tables or named ranges, clean worksheets in Power Query, and keep shared files in OneDrive or SharePoint so refresh works without a gateway.
  • The service can import a workbook's data as a semantic model or upload it as a workbook for Excel for the web.
  • Analyze in Excel gives Excel users a live PivotTable on a published model, with its measures and security, and Export data is for one-off copies.
  • Excel and Power BI share the Power Query engine; use Excel for ad hoc work and inputs, Power BI for governed, refreshed, shared reporting.

Next: Your First Report if you have not built one yet, or on to Connecting to Data.