Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
v-alq-msft
Community Support
Community Support

Hi, @Anonymous 

 

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.

 

Anonymous
Not applicable

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.