Forum Discussion

Willgart's avatar
Willgart
Helper II
8 years ago

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

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.

 

Need Help

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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-Excel-workbook-in-Power/m-p/366442#M15921

    Regards,
    Lydia

    • Willgart's avatar
      Willgart
      Helper II
      8 years ago

      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 :)

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        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