Excel table add to data model and Power BI automatic refresh?

[Willgart]

Hi,

I have an Excel file with some tables in it.

these tables has been added to the data model (Power Pivot) into my Excel document.

I save this document in SharePoint and connect it to Power BI online to create some reports and dashboard on top of it.

everything is working fine.

Except 1 point:

the data model is not automatically refreshed.

a user editingthe Excel file, must click refresh all before saving it, else the data model is not updated.

Is it possible to automate this refresh?

so if the user forgot to click the refresh all button, I can insure that the data model is up to date?

if yes, where and how?

thanks.

[v-yuezhe-msft]

@Willgart,

Have you configured on-premises gateway in your machine? You would need to add the Excel data source within gateway(or you can use personal mode gateway instead), then set schedule refresh for your dataset.  For more details, please take a look at this article.

There is a similar thread for your reference.
http://community.powerbi.com/t5/Integrations-with-Files-and/Data-gateway-mode-required-to-refresh-an...

Regards,
Lydia

[Willgart]

Hi,

I'm not using external data in this Excel document. Only data stored and managed within the Excel itself and the file is store in SharePoint, so there no need to rely in my gateways.

But I do some tests, and add in my data model ome data coming from an on premise SQL database. I was able to refresh these data from power bi automatically, but the data from Excel were not refreshed.

I also test by using the Power Query feature to import the data into Excel (instead of clicking add data to model) but the Power BI said dataset setting page said its not supported. so not able to refresh at all with this option.

So for now its not working, my user have to click the refresh all button. But I hope I'll find the solution 🙂

[v-yuezhe-msft]

@Willgart,

Could you please explain the following statement? Which option do you use in Excel to add data from tables to PowerPivot data model? Where do you store these tables originally?

I have an Excel file with some tables in it.

these tables has been added to the data model (Power Pivot) into my Excel document.

Regards,
Lydia

[Willgart]

when you want to add an Excel  table into Power Pivot, you have 2 options:

* power query and M

* add data to model (power pivot menu, add to data model option)

so I'm using the second option. my Excel data and my power pivot model are in the same document, is not 2 different ones.

the data are directly sent to the model without using Power Query.

My document is stored in SharePoint and accessible to multiple users, editable online.

but unfortunatly, in Power BI, the data never refreshed automatically.

when a user edit the content of the table, its not reflected until a user clicks the refresh all button in Excel (could be local or online, no matter, but a user has to click refresh)

is the automatic refresh not supported in Power BI for this scenario? 

I'm not able to find any information on this subjects.

[v-yuezhe-msft]

@Willgart,

Copy the data of excel(file1) table and paste it in another excel file(file2), then use PowerPivot->Manage->Get External Data->From other sources-> Excel file option in to connect to file2 in file1, after that, upload file1 to OneDrive.

In Power BI Service, connect to file1 to create report, then use personal gateway to refresh your dataset. The whole process is similar as that described in the following similar thread.

http://community.powerbi.com/t5/Integrations-with-Files-and/Data-gateway-mode-required-to-refresh-an...

Regards,
Lydia

[Willgart]

I know it

but its not an option.

the file and the powerbi reports & dashboards are in a apps in powerbi shared within the company.

we cant use the personnal gateway  to maintain it.

[v-yuezhe-msft]

@Willgart,

You can use on-premises gateway instead of personal gateway. It doesn't support to automatically refresh your dataset in your original scenario.

Regards,
Lydia