Forum Discussion

Willgart's avatar
Willgart
Icon for Helper II rankHelper 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 o...
Need Help
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
    Icon for Helper II rankHelper 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

      • Willgart's avatar
        Willgart
        Icon for Helper II rankHelper II
        8 years ago

        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.