Get data from Power BI DataSet Import option? - Excel

[Anonymous]

Hi,

I'm new here and would like to know if I can easily Get Data from a Power BI DataSet and use it in an Excel Data Model.

Currently, I can create a DataModel in an Excel workbook and Publish it to Power BI.

In Power BI Service, I can click on "Analyze in Excel" and create a Live Connection to the DataSet but it's not in the DataModel

If I want to bring it back down into a DataModel, there is no "Import" option like there is with Get Data from Analysis Services (Import).

Does anyone know of an easy way to "Get Data from Power BI Dataset (Import)"?

[karo]

Hi,

Any updates?

I am looking a way to build "composite models" in Excel using connection to Power BI Dataset or

to import data from Power BI Dataset into Excel ...

Regards,

Karo

[Anonymous]

Were you able to get this working? I'm trying to do the same thing. 
 
Closest I can get is to Get Data → From Other Sources → From OLEDB, using the Power BI connection string. However, my permissions quickly expire, presumably because the OLE DB connection string doesn't allow the Integrated Security property, which *is* used in the Analyze in Excel connection ("Integrated Security=ClaimsToken;")…

[v-danhe-msft]

Hi @Anonymous,

Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?

Regards,

Daniel He

[v-danhe-msft]

Hi @Anonymous,

Based on my research, you could use the Power BI publisher for Excel to import data from Power BI in Excel.

Reference:https://docs.microsoft.com/en-us/power-bi/publisher-for-excel

Regards,

Daniel He

 

[Anonymous]

Hi, I'm sorry for the delayed response. You are correct that the Power BI Publisher Add-in will allow you to connect to the Power BI DataSet but as a connection only. I am looking to move the data from the Power BI Dataset into the Excel DataModel in one step.

My attempts to do this through Power Query M using the https://msdn.microsoft.com/en-us/query-bi/m/analysisservices-databases have not worked.

I do not know if this is because I'm unable to connect or I have bad syntax. However, I would rather not use this option and would like a simpler way than having to write Power Query M.

There are many ways to "Import" from Analysis Services - (I'm using the term import in a reserved way here, as described in https://docs.microsoft.com/en-us/power-bi/desktop-use-directquery)

"Import – the selected tables and columns are imported into Power BI Desktop. As you create or interact with a visualization, Power BI Desktop uses the imported data. You must refresh the data, which imports the full data set again, to see any changes that occurred to the underlying data since the initial import or the most recent refresh"