Forum Discussion
Auto Import Power BI Dataset to Excel
Guys, I’m a kinda novice when it comes to Power BI (but not Power Query obviously), so, I don’t really know that much about this programme capabilities, one thing I know though, its great auto-refresh feature that I’d like to take advantage of. Problem is, we’re still that kind of Excel organisation, the end users prefer to use it to view the reports, hence, I’d like to play with the Power BI as a backend. I’d like to have all my queries that make my final data model running there in Power BI, I’d like them to auto-refresh daily at some hour, and that’s a good part of workload is gone. Now, the question that I’ve for a long time now, is there a way to have the Power BI dataset linked to a fixed existing Excel file so once the Power BI finishes its auto-refresh, the data model is loaded in a table to this excel file even if it is closed? I fully understand there’s no direct way to do that, not yet at least, but perhaps a workaround using Power Automate or this new feature of Power BI Dataflow, any insights perhaps? If none of these is an option, is there a way then to connect to a query in Power BI thru Excel? But the purpose here is to merge or append it with another query, I mean, to have it as an existing query in the same Excel file, not just as a connection, that’d be something too.
6 Replies
Hi Anonymous
Excel contains a live connection to the Power BI Dataset via the Pivot Table. This Pivot Table can be set to refresh upon opening and refresh on a scheduled basis. If is does not refresh, the data will be updated when some changes the pivot table filter or selection.
If you want to download the Dataset tables into Excel Table, it is possible using two approaches:1 - Power Query - Use Power Query to pull the Power BI Analysis Service data into a query then publish the data.
2 - Table External Data Query - Use DAX or MDX query to pull data into the Table. Here is one way to achieve this from the Pre-Power BI era: Import Data from Tabular Model in Excel Using a DAX Query - SQLBI and an updated version Consuming a DAX query in Excel - Unplugged #5 - YouTube
Both approaches could work, but I would not recommend.Anonymous - downloading data as table into Excel is adding technical debt - leads to duplicate and redundancy of data, and encourages end user to wrangle with the data. Power Bi provide an automated way to create one dataset, but download to excel creates multiple copies.
The recommend approach is to use the Pivot Table in Excel - this shows list of the Data Model the users has access:
Clicking on the equivalent option is Get Data will also launch a Pivot Table:
However, with the "From Database" in the above there is drop that contains the "Import" option from Analysis Services:
This will prompt you for server and cube, and then use Microsoft Account Credentials to log in.
Important - the workspace connection URL is only available if the Workspace is backed by Premium Capacity. Dataset in Shared Capacity don't have the equivalent URL, Dataset Names and Authetication process, so it will not work.