PowerBI & Excel together - auto update pivot tables.

[Barslund]

Hi,

I'm an experienced Excel user but relatively new to PowerBI, and I'm trying to figure out how the two work best together.

I've understood that I can create the following workflow / structure:

• Export .ocd file from PowerBI
• Build Pivot Tables in Excel that links directly to PowerBI
• Create more advanced calculation models based on the pivot tables
• Connect to PowerBI to the Excel file through "Connect, Manage and view Excel in PowerBI
• Have the file stored on OneDrive for auto update purposes
• 'Schedule refresh' of the Excel file in PowerBI to have the most updated data.

To make this work, I'm wondering if the "Schedule refresh" from PowerBI will force Excel to update its internal pivot tables, or if it's necessary to build some kind of VBA code to make this refresh happen?
The goal is to build a report that will update automatically, so I don't have to open the Excel file to make this happen.

Additionally, must the file must the file be stored on a OneDrive or is there other possibilities to make this work?

Thanks in advance.

 

[cyrilbrd]

Hi, (happy new year)
Scheduled Refresh is available for the paid version only.
This said the free version updates as well upon opening and refresh.
You need not link the pivot tables but rather only tables.
Any data added on said table will be reflected in PBI, you only need to refresh your pbix file.
Having the file in OneDrive will just facilitate the communication in between PBD and the source, but you might not be limited to this cloud service.