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
maden79
New Member

Dataset from dynamic worksheet

Help!

 

I'm trying to connect a dataset to a Dynamic Worksheet so that I can refresh data from CRM. To upload the dataset into PowerBI I'm prompted to convert my dynamic spreadseet into a table (Ctrl+t), however when I do this I can a message saying all external data links will be lost.

 

How can I get this as I need this dynamic spreadsheet to be refreshed regularly from CRM.

 

Any help would be appreciated.

6 REPLIES 6
martinbell
New Member

Did you get a solution to this.  I am having the same trouble

+1 to this

Jimmy801
Community Champion
Community Champion

Hello

@rigreen, @maden79 , @martinbell 

 

there are multiple ways to access worlbooks/worksheets dynamically. But to answer your question I absolutly need your real life example (where is/are the file/s, structured all the same way? What content you exactly have to access (table, sheet, range?))

Without this information it's impossible answer... it would only be guessing

 

Have a nice time

 

Jimmy

Hi Jimmy,

 

Thanks for your reply.

 

I am taking a dynamic worksheet from Dynamics 365, authenticating the data connection in Excel and then saving it to One Drive for Business.

 

The idea is to build a report in Power Bi Report builder that uses this refreshable worksheet as the data source from Power Bi.

 

All goes well until I try to upload the Workbook to Power Bi as a dataset as I am forced to turn the excel data into a table which then kills all of the data connections and refresh ability of the report dataset. 

 

Hello

Sorry, not really an expert with dynamics, only Excel.
And I don't understand this argument
"dataset as I am forced to turn the excel data into a table which then kills all of the data connections and refresh ability of the report dataset."
With Power query you can only access the raw data present in the file.. so no connection. But if you are interested to get data from dynamics 365... why don't query this source directly?
Have a nice time
Jimmy
v-qiuyu-msft
Community Support
Community Support

Hi @maden79,

 

After you click the Export to Excel -> Dynamics Worksheet, did you save the file as .xlsx file? I have exported the Sales-> Opportunities to Dynamics worksheet .xlsx file, and get data from this Excel in Power BI desktop version 2.44.4675.521. All is fine after publish to the service.

 

In your scenario, please try to use the same desktop version as ours. You can also test with the Sales-> Opportunities record type. Besides, to get data from Dynamics CRM, there are content packs which you can utilize. See: Microsoft Dynamics CRM content pack for Power BI.

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

Top Solution Authors
Top Kudoed Authors