Ravindra BagaleCourses & study guides मराठी Track your progress

24. Power Pivot and the Data Model

24.10 When to Move to Power BI

By Last updated:

Example first. Ravindra Bagale's Data Model now has four tables, five measures and two monthly files. The managers want to open the report on their phones, see it refresh every morning, and have each city lead see only their own city. Those three wishes are the signal: the model is ready to move to Power BI, and it can be imported in one step.

Excel Data Model or Power BI — in 30 seconds

What: Power BI Desktop uses the same engine as the Excel Data Model: the same tables, relationships, Power Query steps and DAX measures. It adds interactive visuals, publishing to the web service, scheduled refresh and row-level security.

Why: Moving at the right time saves work: your Power Query steps and DAX measures come across instead of being rebuilt.

Tiny example: Power BI Desktop › File › Import › Power Query, Power Pivot, Power View › pick the workbook. Queries, tables, relationships and measures come across.

First look at the data (before → after):

Excel Data Model or Power BI: before and after tables, fictional Blinkit Maharashtra data

Expected result — Time to move to Power BI

Need Why Power BI
Many viewers, phones and browsers Publish once to the Power BI service and share
Daily or hourly automatic refresh Scheduled refresh in the service
Each city lead sees only their own rows Row-level security
Interactive visuals: cross-filter, drill-down, maps Built into every report page
Data grows into millions of rows from several sources Built for large models and many sources
Before table and formula as text

Before — Still fine in Excel

Need Excel Data Model
One analyst, a few colleagues by email or shared drive Yes
Monthly refresh by clicking Refresh All Yes
Printed or emailed tables Yes
Cell-level what-if work next to the model Yes, Excel is better

Formula / click path

Power BI Desktop › File › Import › Power Query, Power Pivot, Power View
Pick the .xlsx › Start › choose Copy data or Keep connection for sheet tables › Close

Do it step by step:

  1. Clean up the Excel model first: clear table names, measure names and a marked Calendar table.
  2. Open Power BI Desktop.
  3. Click File › Import › Power Query, Power Pivot, Power View.
  4. Choose the workbook and click Start.
  5. For tables that live on Excel sheets, choose Copy data (or keep the connection), then click Close.
  6. Check the Model view: the same relationships appear. Check that each measure gives the same total as in Excel.
  7. Build the visuals and publish; continue with the Power BI course.

A quick decision list

  1. Is the report for you and a few people, refreshed by hand? Stay in Excel.
  2. Do people need it on phones or in a browser, refreshed automatically? Move to Power BI.
  3. Must each person see only their own rows? Move to Power BI and use Row-Level Security (RLS).
  4. Do you need what-if inputs, cell formulas and printing beside the numbers? Keep that part in Excel; you can still connect Excel to a published Power BI model later.

Continue learning: the Power BI course starts from Power Query and goes through Data Modelling and DAX, so everything in this module carries over.

Practice task (try it before you open the answer):

  1. Write down three needs of your own report.
  2. Mark each "Excel" or "Power BI" using the tables above.
  3. If you have Power BI Desktop, import this module's workbook and compare Total Sales.
Answer

After import, Total Sales in Power BI must show ₹2,315, Customers 6 and Pune Sales ₹940, exactly as in Excel. If a number differs, check that all relationships came across in Model view.

Ravindra Bagale's Tip

Build the model in Excel the "Power BI way" from day one: Tables, a star schema, a Calendar and measures instead of cell formulas. Then the move is an import, not a rebuild.