none
Automatic refresh of workbook RRS feed

  • Question

  • Hi guys,

    I have a problem, to which I haven't found a solution yet.

    Situation: We have different data sources (google sheet, csv, web-connections, file structure,...) which need to be combined in one Master Excel Sheet, automatically/manually refreshed and be seen/used by different people in my company.

    Ideal situation: Google Sheet in Google Drive -> import to power query -> add further data sources and combine these -> save to google drive again -> refresh on command

    In Power BI an automatic refresh is possible for an xls file or pbix file, which is on onedrive. Is it possible to refresh a file (workbook) in onedrive with any other source, without having to open the excel workbook locally?

    Trying to find workarounds for the problem at hand and would be very thankful for any suggestions, tipps or comments!

    Thanks

    Tanja

    Tuesday, February 16, 2016 6:46 PM

Answers

  • You should be able to setup scheduled refresh for an xlsx or pbix that combines the data sources you're describing. If you're only using web resources, it should work without you having to configure a gateway. If you're also using on premises data sources (e.g. if some the files comes from a share on your network), you'll need a gateway.

    More info on the gateways can be found here:

    https://powerbi.microsoft.com/en-us/documentation/powerbi-personal-gateway/

    https://powerbi.microsoft.com/en-us/documentation/powerbi-gateway-enterprise/

    Ehren

    Friday, February 19, 2016 12:22 AM
    Owner
  • Hi Tanja. I'm not entirely clear on what you're trying to do, but would the following work for you?

    • Put the data you'd like to be able to manually update in an Excel file.
    • Reference this Excel file from Power BI Desktop.
    • Publish the pbix file to powerbi.com and set up scheduled refresh.

    If you're okay with not being able to refresh the data in Excel online, doing the following would also be an option:

    • Put the data you'd like to be able to manually update in an Excel file.
    • Build your queries to pull from external data sources in the same Excel file.
    • Publish the Excel file to powerbi.com and set up scheduled refresh.

    Ehren


    Tuesday, February 23, 2016 8:25 PM
    Owner

All replies

  • Hi Tanja,

    Have you tried Power Update?

    http://www.power-planner.com/Products/ProdID/10

    James

    Wednesday, February 17, 2016 7:53 PM
  • You should be able to setup scheduled refresh for an xlsx or pbix that combines the data sources you're describing. If you're only using web resources, it should work without you having to configure a gateway. If you're also using on premises data sources (e.g. if some the files comes from a share on your network), you'll need a gateway.

    More info on the gateways can be found here:

    https://powerbi.microsoft.com/en-us/documentation/powerbi-personal-gateway/

    https://powerbi.microsoft.com/en-us/documentation/powerbi-gateway-enterprise/

    Ehren

    Friday, February 19, 2016 12:22 AM
    Owner
  • Hi James,

    thanks so much for the tip! Really cool tool.

    Tested it and seems to work pretty well. ...BUT only helpful, if it's running on a computer that is always running, right?

    Regards

    Tanja

    Tuesday, February 23, 2016 3:28 PM
  • Hi Ehren, 

    thanks for the reply.

    I know that I can schedule refresh for a pbix file, which I could also create with my data sources. But then I don't have the data in table form (googlesheet or excel), which can be edited. 

    And if I have the excel sheet in one drive and open it in excel online, I can't seem to refresh the data sources (momentarily google drive files).

    Regards

    Tanja

    Tuesday, February 23, 2016 3:57 PM
  • Hi Tanja. I'm not entirely clear on what you're trying to do, but would the following work for you?

    • Put the data you'd like to be able to manually update in an Excel file.
    • Reference this Excel file from Power BI Desktop.
    • Publish the pbix file to powerbi.com and set up scheduled refresh.

    If you're okay with not being able to refresh the data in Excel online, doing the following would also be an option:

    • Put the data you'd like to be able to manually update in an Excel file.
    • Build your queries to pull from external data sources in the same Excel file.
    • Publish the Excel file to powerbi.com and set up scheduled refresh.

    Ehren


    Tuesday, February 23, 2016 8:25 PM
    Owner
  • Hi Ehren,

    sorry for the late reply. I'm afraid momentarily it's not possible to automate our process in the way we need/want.

    We would actually need to be able to refresh the data in excel online. Since the data in power bi is not editable as a table anymore (the excel sheet is edited during the day). Therefore the refresh needs to take place in excel on a desktop at the moment. 

    But thanks anyway for your help and support!

    Regards

    Tanja

    Thursday, March 24, 2016 10:52 AM