cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper IV
Helper IV

Analyze in Excel and re-connect to PBI (no full access to dataset)

Dear all,

 

hoping to get some help here. I am working on some reports to which I don't have full access (they are shared with me but do not belong to my working space).

 

I am using the 'Analyze in Excel' function to download the dataset, with the idea of doing some edits and the re-connect to a new PBIX file which I can shape as I need. 

 

The 'Analyze in Excel' function only generates a 'monster' ODC file with pivot tables which is impossible to manage and edit in Excel because it is simply too big and it crashes.

 

Question 1: is it possible to 'intervene' in the ODC generation and get a simpler .csv or .xls. ? 

Question 2: if not, is it possible to somehow edit the model (e.g. adding columns) of a report I don't have full access to? And removing what I don't need?

 

Hope this is somehow understandable 🙂

Final  note: the option of exporting into excel only the visuals I need does not work, since unluckily I need a different structure.

 

Many thanks!

2 REPLIES 2
Highlighted
Community Support
Community Support

Re: Analyze in Excel and re-connect to PBI (no full access to dataset)

Hi, @Chateaux83 

 

There is no direct way to 'intervene' in the ODC generation and get a simpler .csv or .xls. When you open the file in Excel, an empty PivotTable and Fields list appears with the tables, fields, and measures from the Power BI dataset. You can create PivotTables, charts, and analyze that dataset just as you would work with a local dataset in Excel.

 

The .ODC file has an MSOLAP connection string that connects to your dataset in Power BI. When you analyze or work with the data, Excel queries that dataset in Power BI and returns the results to Excel. If that dataset connects to a live data source using DirectQuery, Power BI queries the data source and returns the result to Excel.

 

Users can connect to datasets through Analyze in Excel if they have permission for the underlying dataset. A user could have this permission in several ways, such as having the Member role in the workspace containing the dataset, having a report or dashboard shared to them that uses the dataset, or having Build permission for the dataset, in either a workspace or an app that contains the dataset. Read more about Build permission for datasets.

 

For further information, please refer to the official document .

 

Best Regards

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Highlighted
Helper IV
Helper IV

Re: Analyze in Excel and re-connect to PBI (no full access to dataset)

Hi,

 

thank you for your reply.

The problem is that the pivot table is not actually of any help since the dataset is too big and I simply cannot analyze it in Excel Pivot table -  it crashes  after I add a couple of fields. 

 

So my question was whether there is a way to downsize the dataset before exporting - i.e. removing some measures or tables. 

 

I guess that is a no. 

Thanks for the help!

 

 

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors