Excel Workbook refresh Setup

[cmcreynolds]

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.

[Greg_Deckler]

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]

@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.

[PowerBIGuy]

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]

@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]

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. 

[cmcreynolds]

Yes, I have follwed those instructions and my data does not change in the Excel workbook.

[Seth_C_Bauer]

From my understanding of what you are saying, you are trying to have Power BI refresh your data in Excel, then refresh data in PoerBI. It isn't designed to do that. The gateway will take your data in the Workbook "as-is" and refresh the elements in Power BI. It won't refresh your data in the source first...

[cmcreynolds]

I thought part of PowerBI was to do refresh the workbook itself, especially when PowerQuery was used to pull the data. 

I suppose I've been chasing rabbits, then.

[cmcreynolds]

The tables explain that differently - I totally interpreted it as a workbook using Power Query "to connect to and query data from any listed online data source and load data into the Excel data model." 

(Under "Excel workbook on OneDrive")

https://support.powerbi.com/knowledgebase/articles/474669-data-refresh-in-power-bi

[Greg_Deckler]

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 workbook

2. 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 opportunities

4. Saved Excel workbook to OneDrive for Business

5. Went into Power BI Service and Get Data | Files | One Drive for Business | [my file] | Connect | Connect, Manage and View Excel in Power BI

6. File showed up under Reports

7. Clicked ellipses | Schedule Refresh

8. Entered credentials (I used OAuth2)

9. Set schedule

10. Went into CRM Online and entered a new opportunity

11. 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. Nothing

12. Clicked ellipses and it said something to the effect that refresh failed because file was open

13. Closed file

14. Refresh now once again

15. Clicked ellipses, success this time

16. Opened file, looked at data model and it had 143 opportunities

17. Stood up and did an awkward dance

18. 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.

[cmcreynolds]

@Greg_Deckler

Yes, I"ve tried bringing it in as a report as well as a dataset, but it never refreshed my data. Hmmmm....I'm glad it worked for you, though. Did it save a new Excel file for you? or was it just new data?

[Greg_Deckler]

Not exactly sure what you are asking, but I can go out to my OneDrive for Business in Windows Explorer, open the Excel file and it has 143 Opportunities instead of 142 so it refreshed it and saved it back to my OneDrive for Business.

This is actually pretty wicked cool as doing it this way means that your OneDrive file is always up to date and Power BI is refreshing it for you versus you loading your Excel file into Power BI and then it is really only up-to-date within the Dataset within Power BI Service. Nifty.

So, if you click on your file under Reports, click the ellipses, do a Refesh Now, let it spin and such until the little Excel icon appears again, click the ellipses and FREEZE. Look at the "Last refresh" within in the little pop-up window, what does it say? Does it say that it succeeded? If it says "Last refesh failed", what is the failure?

[cmcreynolds]

@Greg_Deckler

Well, guess what happened - it worked! And it saved a new version of the file (I wasn't expecting that) with new data.

Well, now I have some hope. Thanks!

[Greg_Deckler]

Cool beans.

[cmcreynolds]

@Greg_Deckler @PowerBIGuy

So, I built another report pulling data from Salesforce and two smaller tables that are housed in anothr OneDrive folder. It does not work - when I try to schedule refresh, I get the message:

I can't figure out which of the sources don't support refresh. My report that worked yesterday connects to many of the same files as the new one. I HAVE merged some Power Query queries - do you think that's the issue?

[cmcreynolds]

Exactly what I did, the failures were always obscure - saying that there were certain queries that failed. I've already taken them out.

I'll try again and let you know what error I get.

[PowerBIGuy]

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.

[cmcreynolds]

@PowerBIGuy

I will try putting them into the Workbook as tables - but what about when I have to update those tables?

[PowerBIGuy]

Since your workbook is stored in one drive if you update the excel table within the file the data should automatically be updated every hour within power BI.

https://support.powerbi.com/knowledgebase/articles/424871-excel-data-in-power-bi

[PowerBIGuy]

Also FYI. Excel files that contain excel tables do not support auto refresh because they get refreshed every hour

automatically. I dont have one drive for business or CRM so i cant recreate your process on my end. but from the sounds of if you are mxing datasets that support refresh with datasets that do not. i dont see why it should cause a problem if each one is setup correctly but again i cant test it on my end. sorry

https://support.powerbi.com/knowledgebase/articles/424871-excel-data-in-power-bi