Automate Power Query and Power Pivot Refreshes in Excel with remiCrystal

For years, Excel has been the go-to tool for business analysis, dashboards, and reports. But as data sources multiply and reporting deadlines tighten, manually clicking “Refresh All” just isn’t sustainable.

If you’ve ever opened a workbook first thing in the morning, only to watch it slowly refresh multiple Power Query connections before you can even start your day, you already know the pain. That’s where remiCrystal comes in — your new way to automate Power Query and Power Pivot refreshes in Excel.

What Is Power Query?

Power Query is the engine inside Excel that connects to and transforms your data. It’s what happens every time you click Data → Get Data → From…

Think of it as Excel’s built-in ETL (Extract, Transform, Load) tool. You can connect to:

• SQL databases

• CSV and Excel files

• SharePoint and OneDrive folders

• Web APIs and OData feeds

From there, you can filter, merge, pivot, or reshape data before loading it into your workbook or data model.

In older versions of Excel, these were called “connections.” In modern Excel, they’re all handled by the Power Query M engine. That means if your report pulls in data from any external source, it’s almost certainly using Power Query under the hood.

How Power Pivot Fits In

Once Power Query has cleaned and loaded your data, Power Pivot takes over. Power Pivot is Excel’s data modeling layer — it’s where you build relationships, calculations, and KPIs using DAX formulas.

In most modern workbooks, Power Query feeds Power Pivot. You might:

• Pull raw data from SQL using Power Query

• Load it into the Power Pivot data model

• Build PivotTables and charts that summarize metrics

This Power Query → Power Pivot pipeline gives Excel near-Power BI capabilities — but it also creates a new challenge: keeping everything refreshed and up-to-date automatically.

The Problem with Manual Refresh

The typical workflow looks like this:

1. Open Excel.

2. Click Refresh All.

3. Wait for Power Query to load data.

4. Wait again while Power Pivot recalculates models.

5. Save the workbook.

6. Export or email the report.

Now multiply that by 10 reports, or 10 users, or 10 clients — and you see why IT teams and analysts are desperate for automation.

You can’t realistically rely on users to open and refresh files every morning. And while Power BI has a built-in scheduled refresh service, Excel doesn’t.

That’s exactly the gap remiCrystal fills.

How remiCrystal Automates Power Query and Power Pivot

RemiCrystal has always supported Excel automation — connecting to workbooks, refreshing data, exporting results, and delivering reports. But here’s the key insight:

When remiCrystal refreshes an Excel workbook’s connections, it’s actually refreshing Power Query.

That’s because every Power Query in Excel is stored as a connection that the Excel object model can refresh programmatically. When remiCrystal runs a job, it:

1. Opens Excel in a secure background session (no user required).

2. Triggers the workbook to execute all Power Query queries and data connections.

3. Waits for the refresh to complete.

4. Recalculates formulas and Power Pivot models.

5. Saves the workbook and exports it to PDF, XLSX, or CSV etc

6. Distributes the output by email, file share, or SharePoint and more.

✅ Result: Your Power Query data is refreshed.

✅ Your Power Pivot models are recalculated.

✅ Your entire Excel report is ready to be distributed — automatically.

No human clicks required. No Excel windows popping up on a desktop. Just scheduled, repeatable automation.

Real-World Scenarios

1. Finance and Accounting: Your monthly P&L workbook connects to multiple SQL databases via Power Query and aggregates them in Power Pivot. With remiCrystal, you can schedule it to refresh every night at midnight and deliver a PDF summary to executives before they wake up.

2. Sales and CRM Dashboards: Sales teams often use Power Query to pull CRM data from Salesforce or Dynamics 365. Instead of manually refreshing and exporting charts, remiCrystal can do it on a schedule — updating the Excel file and sending the latest figures to your team automatically.

3. Operations and Inventory: Operations teams often have Excel models that merge CSV exports from ERP systems. Those Power Query transformations can take minutes per file. With remiCrystal, that refresh happens unattended — ensuring inventory reports are always up-to-date.

Enterprise-Ready Scheduling and Delivery

Because remiCrystal was originally designed for enterprise report automation, you also get:

• Secure credential management for SQL, SharePoint, or ODBC sources

• Parallel job execution for multiple reports

• Logging and error alerts when a refresh fails

• Flexible destinations — email, disk, FTP, or SharePoint

So you’re not just refreshing Power Query — you’re orchestrating a complete data-to-delivery pipeline.

The Bridge Between Excel and Power BI

One of the biggest advantages of Power Query and Power Pivot is that they use the same data engine as Power BI. That means automating Excel refreshes with remiCrystal can be your bridge into Power BI workflows:

• Use the same data logic in Excel and Power BI.

• Schedule Excel refreshes alongside Power BI dataset refreshes.

• Distribute outputs to users who don’t need Power BI licenses.

• Schedule automated Power BI DAX queries from within remiCrystal

In essence, remiCrystal becomes your universal automation hub — connecting Power Query, Power Pivot, Excel, and Power BI in one workflow.

remiCrystal makes that possible.

Schedule your Power Query refreshes. Recalculate Power Pivot models. Deliver the latest Excel insights automatically — all without ever opening Excel.

Your reports deserve to run themselves. Let remiCrystal make that happen.