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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
IamTDR
Responsive Resident
Responsive Resident

Dataset from Report Service to Excel as a Table

Just double checking to see if I am understanding this correctly.
1. Create Dataset using Power BI Desktop

2. Publish to Power BI Service

3. Open Excel on desktop and then connect to dataset (Data--Get Data from Power Platform)

4. Select Dataset

 

Now I am able to create a  pivot table.  Whatif I rather have access to the tables instead?  Is this possible?

I am making a excel worksheet for a department based on a created dataset.  I would like to connect directly to the Power BI dataset so that my end users would not need datawarehouse 'read' access.  They would have to refresh the worksheet, for which I can create a button/marco to do so.
I am currently copying the dataset and using excel power query to create the tables in Excel.  This method requires end users to have read access to the datawarehouse.

The request is being done in Excel as my end users need to consistently forecast units/sales against actual data.

1 ACCEPTED SOLUTION
IamTDR
Responsive Resident
Responsive Resident

Figured this out yesterday.  Noting the steps I took in case anyone else is interested.


Connect Power BI Dataset to Excel as a Table (Not Standard Pivot Table)
1. Open Excel file and Connect to Power BI Dataset
2. Create a Pivot Table w a Measure Included
3. Double Click on Measure results in Pivot Table and follow to next Screen
4. Right-Click within table and Select 'Edit Query'
5. Within Query Content type "Evaluate tablename" (Note no text characters in front of table name)
6. Original Pivot Table can now be Deleted
7. Data Connections, good practice to rename Connection

View solution in original post

1 REPLY 1
IamTDR
Responsive Resident
Responsive Resident

Figured this out yesterday.  Noting the steps I took in case anyone else is interested.


Connect Power BI Dataset to Excel as a Table (Not Standard Pivot Table)
1. Open Excel file and Connect to Power BI Dataset
2. Create a Pivot Table w a Measure Included
3. Double Click on Measure results in Pivot Table and follow to next Screen
4. Right-Click within table and Select 'Edit Query'
5. Within Query Content type "Evaluate tablename" (Note no text characters in front of table name)
6. Original Pivot Table can now be Deleted
7. Data Connections, good practice to rename Connection

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors