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.

[cmcreynolds]

Okay - so, I just painstakingly checked - both of my reports use THE SAME "static" (for lack of a better term...really) Excel tables. Same place - One Drive.

The only difference is the report that DOES NOT work uses "Merge" in Power Query. Honestly, that is THE ONLY difference. Thoughts?

[cmcreynolds]

Right, but I have many reports that also read from those tables. So, I can't make connections to the smaller tables? (I say "smaller" but they basically translate Salesforce ID fields into more usable text)

[Seth_C_Bauer]

Couple things.

1) The PBI Personal Gateway will not help you if you are trying to perform a refresh of an Excel file in One Drive without any tie to Power BI.

2) If you were loading your Excel file into Power BI then I would direct you here. And it will outline what is possible.

3) The page you listed relates to how the Personal Gateway relates to a bunch of different scenerio's of how data sources are compiled. The table still defines the Excel file and Power Query scenerio as a "datasource" - which, as highighted earlier in the page, outlines that the context of the entire page fall under this statement:

"When you refresh data in Power BI, you're updating data in the dataset with new and updated data from data sources."

You are in charge of updating your data source

Power BI will take that data from the data source and update the dataset.

[cmcreynolds]

Right, I understood the differentiation between dataset and data sources - I guess it just lost me the requirement that it had to be loaded into PowerBI.

So there's no automatic (scheduled) way of refreshing data sources, then? (Power Update is out there, but pricey)