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 o...
Need Help
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
Helper 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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

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

    Regards,
    Lydia

    • Willgart's avatar
      Willgart
      Helper II
      8 years ago

      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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        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