Okay, maybe I'm approaching this all wrong, conceptually, but nothing I've done works - so please help clarify what I want/need is setup correctly. I want coworkers to see pivot tables dynamically (refreshed a couple times a day, but at least daily) in my Excel workbook. I don't need charts/dashboards (yet?) - just summarized pivot tables.I pull data using PowerQuery from Salesforce and in-house tables on OneDrive and Sharepoint; I then manipulate it in the DataModel and display a PowerPivot table.I have shared these workbooks both on Sharepoint and OneDrive with coworkers.I have PowerBI Gateway and I've tried scheduling refreshes for these - both as reports and datasets, BUT no data is "new" in the workbooks (when there isn't an error). I have to go into the workbooks and manually refresh. I just want my coworkers to have new data without me having to manually refresh. Have I structured something wrong? Am I trying to make PowerBI do something it wasn't intended? Any suggestions? Thank you.

As long as power query is connected to a supported data source and the data is loaded into the model and not into excel it should work. Now your "in-house" data is a lil different. I would take those lookup tables and try to store them directly in the same power query file as excel tables.

OK, hoping to put this one to rest. Short answer is YES, you can do what you want to do in Power BI. Not sure of the problems you are having with refresh but here is what I did, hopefully this will help. 1. Created new Excel workbook2. Used Power Query to connect to CRM Online (sorry I don't have a Salesforce instance to test with so best I could do)3. Imported OpportunitySet into my data model, imported 142 opportunities4. Saved Excel workbook to OneDrive for Business5. Went into Power BI Service and Get Data | Files | One Drive for Business | [my file] | Connect | Connect, Manage and View Excel in Power BI6. File showed up under Reports7. Clicked ellipses | Schedule Refresh8. Entered credentials (I used OAuth2)9. Set schedule10. Went into CRM Online and entered a new opportunity11. Back to Power BI Service, didn't feel like waiting so clicked ellipses next to my file under Reports and did a "Refresh Now"11. Nothing12. Clicked ellipses and it said something to the effect that refresh failed because file was open13. Closed file14. Refresh now once again15. Clicked ellipses, success this time16. Opened file, looked at data model and it had 143 opportunities17. Stood up and did an awkward dance18. Wrote this reply Should work the same if you choose the other option when importing your Excel file except that it will show up under Datasets and that is where you will get the Schedule Refresh and Refesh Now options with the ellipses.

You have alot of moving pieces I’ve broken your process down and have a few questions. Pulled data using PowerQuery from Salesforce - What are your "load to" settings in power query? pbi is expecting create connection only load to model In-house tables on OneDrive - what do you mean by in-house tables? are these csv or excel workbooks? Sharepoint - are you connecting to a sharepoint list? if so again what are your load to settings? Displayed a PowerPivot table. - are you using this excel file as a data source or do you just want to load the whole excel file as is for users to view? if you want to load the whole excel file have you tried storing this file on one drive?

So, when you connected your Excel file to Power BI, how did you do that? Did you use Get Files | OneDrive? If so, did you use 1. Import Data into Power BI2. Connect, Manage and View Excel in Power BI Or, did you import it from a local file? I am trying to recreate what you did so that I can see your issue and attempt to rectify it.

PowerBIGuy "Load to" is set to load to the data model; although, i have some queries I'd like to merge before loading to the data model. I do not load to a worksheet then load that to the datamodel ever, ever, ever. Should I? In-house - I have some dates that I need to categorize and labels I need to abbreviate (for example, Bachelor of Science in Nursing to RN-BSN) Sharepoint - I have used the file path that leads from my favorites; load to data model, but again, I'd like to make "connection only" I would like the whole excel file for all users to view. And yes, I store this on One Drive.

Excel Workbook refresh Setup | Microsoft Fabric Community

24 Replies

PowerBIGuy
Responsive Resident
10 years ago
You have alot of moving pieces I’ve broken your process down and have a few questions.

Pulled data using PowerQuery from Salesforce - What are your "load to" settings in power query? pbi is expecting create connection only load to model

In-house tables on OneDrive - what do you mean by in-house tables? are these csv or excel workbooks?

Sharepoint - are you connecting to a sharepoint list? if so again what are your load to settings?

Displayed a PowerPivot table. - are you using this excel file as a data source or do you just want to load the whole excel file as is for users to view? if you want to load the whole excel file have you tried storing this file on one drive?
- cmcreynolds
  Helper I
  10 years ago
  PowerBIGuy
  
  "Load to" is set to load to the data model; although, i have some queries I'd like to merge before loading to the data model. I do not load to a worksheet then load that to the datamodel ever, ever, ever. Should I?
  
  In-house - I have some dates that I need to categorize and labels I need to abbreviate (for example, Bachelor of Science in Nursing to RN-BSN)
  
  Sharepoint - I have used the file path that leads from my favorites; load to data model, but again, I'd like to make "connection only"
  
  I would like the whole excel file for all users to view. And yes, I store this on One Drive.
  - PowerBIGuy
    Responsive Resident
    10 years ago
    Just want to confrim you've seen the following article. https://support.powerbi.com/knowledgebase/articles/640168-bring-whole-excel-files-into-power-bi
    
    For load to settings you should be selecting create connection only and loading into the data model.
Greg_Deckler
Community Champion
10 years ago
So, when you connected your Excel file to Power BI, how did you do that? Did you use Get Files | OneDrive? If so, did you use

1. Import Data into Power BI
2. Connect, Manage and View Excel in Power BI

Or, did you import it from a local file?

I am trying to recreate what you did so that I can see your issue and attempt to rectify it.
- cmcreynolds
  Helper I
  10 years ago
  Greg_Deckler
  
  I have imported the data GetFiles>OneDrive for Business>Import Data and connect, manage, view like you have noticed
  
  I have ALSO tried to import it from a local file (just one that's on my desktop, to try and solve this issue) - but that failed, too.

Forum Discussion

Excel Workbook refresh Setup

24 Replies

Related content

Scheduling Excel Workbook Connection (Dataflow) Refresh

ODBC Connection to Excel Workbooks

Excel Workbook: Microsoft.ACE.OLEDB.12.0 provider not registered

Refresh Data from excel workbook

Excel Power Query Refresh