Power BI Insights: Excel reports; Dataset refresh; Historical data loading

Power BI pros discuss Excel reports, dataset refresh, and historical data loading.

Using Power BI to generate Excel reports

Chris Webb took a look at Excel reports connected to Power BI datasets, which now work in Excel Online, opening up new opportunities with Power Automate and Office Scripts. To start putting this new tech to work, Webb created an Excel file and connected it to a Power BI dataset. From there, he put together a simple report with Excel cube formulas.

Here, the dataset uses data from the UK Land Registry to show property prices by county. He created a script and setup a second Excel workbook to pass a list of county names to the script. Using a Power Automate flow to call the script for each county, Webb generated a report. He wrote:

How is this different from using Power Automate to call the Power BI export API? A paginated report can be exported to Excel but this method gives you a lot more flexibility because it allows you to use a lot more Excel functionality, not jus the functionality that paginated reports can use in its exports. It also gives you a report that is connected live back to a dataset using cube functions, not static data.

Making sense of historical data loading

Gilbert Quevalliers, writing on Four Moo, continued his multi-part series on migrating for Azure Analysis Services (AAS) to Power BI Premium User (PPU). To load historical data in AAS, users turn to SQL Server Analysis Services or Asynchronous REST API, the second of which can be automated with Power Automate.