Auto Import Power BI Dataset to Excel

[Anonymous]

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.

[Daryl-Lynch-Bzy]

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]

Thank you for your inputs, I really appreciate it.

Addressing the power query part, how to connect it to the Power Bi Analysis services please, if you might guide me.

Why wouldn't you recommend these 2 solutions?

[Daryl-Lynch-Bzy]

@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. 

[Anonymous]

Premium license is different from the pro one, right? 

[Daryl-Lynch-Bzy]

Yes - Premium is not about licensing users.  It is getting dedicated server/resource to host the datasets.  What is Power BI Premium Gen2? - Power BI | Microsoft Docs

This is also a Premium Per User option that might work, if you can limit the number of Excel users who refresh the data.  Power BI Premium Per User - Power BI | Microsoft Docs

[Anonymous]

Got it

Yet, it does not serve my needs

It gives you an option either to have it as a pivot table or a connection that cannot be used with the other Excel queries (in case you want to merge or abend one of them with this connection).

I need something allows me either to load the dataset as a table or to get it as a query that I could merge or append with other queries.