none
Power BI Report > using Excel file (hosted on Sharepoint) as data source > how to do? RRS feed

  • Question

  • Hello,

    I am new to Power BI and need some startup pointers to proceed.

    I have a requirement to generate a Power BI report using an excel file as the data source. This excel file is uploaded on a Sharepoint site and is timely refreshed.

    I have got Office 2013 installed on my desktop with Power Query, Pivot, View add-ins enabled.

    Queries

    1. How to set the data source that points to the hosted excel file?
    2. How to ensure that the report always shows the fresh data?

    I would appreciate if some one can share some pointers to these queries.

    Thanks!


    -Vinay Pugalia
    If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
    Web : Inkey Solutions
    Blog : My Blog
    Email : Vinay Pugalia

    Friday, July 24, 2015 5:52 AM

Answers

  • What are you using to try to connect to the Excel file? Although I have tried this in all of the different methods and it works to some degree (see above)

    Power BI Desktop - Choose to connect to an Excel file and you will get an Explorer file browsing pop-up. Click in the "address" bar where it says "This PC > Windows (C:)..." and paste the URL of your SharePoint document library

    Excel - Same as Power BI Desktop

    Power BI 2.0 (the Service) - Choose Local File and do the same thing when given the file browsing window. I ran into an error here where it let me choose the file but said that there was some sort of error that it couldn't find any data.

    • Marked as answer by vinaypugalia Thursday, July 30, 2015 4:47 AM
    Sunday, July 26, 2015 1:34 PM

All replies

  • This probably belongs on the new Power BI community forums, http://community.powerbi.com.

    So, if using Power BI Preview or Power BI for O365, basically you have to upload the Excel file to the Power BI site and that is where the Excel file is hosted. Within the Excel file are the data source(s) that were created when you used Power Query and you set those data sources to automatically refresh. The process for this is different for Power BI Preview and Power BI for O365. Generally much easier with Power BI Preview but in both cases you download and install either the Power BI Personal Gateway (Power BI Preview) or the Power BI Data Management Gateway (Power BI for O365) and this is what actually does the data synchronization.

    Recommend if you are just getting started to go with Power BI Preview because it is the new, new thing and everything will be transitioning to it.

    Now, if you want to do this all locally in your SharePoint site, then your SharePoint environment has to be configured correctly with SQL PowerPivot installed, etc. It is a pretty complex environment to get setup and working in non-trivial (more than a single server) environment, but it can be done.

    Friday, July 24, 2015 8:12 AM
  • Hello Seth,

    Thanks for pointing me to the Power BI community forum link. I have posted my question over there.

    As said, I am very new to Power BI, so if you can try & explain your point again..that would really help!

    And if you specifically explain more on query no. 1, that would really help a lot.

    Thanks!


    -Vinay Pugalia
    If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
    Web : Inkey Solutions
    Blog : My Blog
    Email : Vinay Pugalia


    • Edited by vinaypugalia Friday, July 24, 2015 1:53 PM formatted the content
    Friday, July 24, 2015 12:58 PM
  • I re-read your question.

    Well, you can use Power Query to connect to your Excel file in SharePoint by choosing to connect to an Excel file and then entering the URL of your document library. This worked for me for SharePoint Online but my local SharePoint instance was giving me authentication issues, but that could be because I am on a non-domain computer at home at the moment.

    As for keeping it up-to-date, you install the gateway for your version of Power BI. For O365, you create a data source based upon your Power Query. For Power BI whatever data sources that you have in the file you upload should appear within the interface and you just have to set your authentication credentials.

    Sunday, July 26, 2015 1:02 AM
  • I re-read your question.

    Well, you can use Power Query to connect to your Excel file in SharePoint by choosing to connect to an Excel file and then entering the URL of your document library. 

    I have already tried this way and am able to select only local files. There is no provision to type in the URL of the excel file hosted on share point.

    Seems, I am missing something very basic. Could you please guide me further on this.

    Also, please note that the gateway is already installed. And the excel file which I need to point as a data source, is actually pointing to the back end database to prepare it's models.

    Thanks!


    -Vinay Pugalia
    If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
    Web : Inkey Solutions
    Blog : My Blog
    Email : Vinay Pugalia

    Sunday, July 26, 2015 6:16 AM
  • What are you using to try to connect to the Excel file? Although I have tried this in all of the different methods and it works to some degree (see above)

    Power BI Desktop - Choose to connect to an Excel file and you will get an Explorer file browsing pop-up. Click in the "address" bar where it says "This PC > Windows (C:)..." and paste the URL of your SharePoint document library

    Excel - Same as Power BI Desktop

    Power BI 2.0 (the Service) - Choose Local File and do the same thing when given the file browsing window. I ran into an error here where it let me choose the file but said that there was some sort of error that it couldn't find any data.

    • Marked as answer by vinaypugalia Thursday, July 30, 2015 4:47 AM
    Sunday, July 26, 2015 1:34 PM
  • I had tried with Excel 2013 - Choose to connect to an Excel file and got an Explorer file browsing pop-up. But did not proceed further as it was prompting me to select a local file.

    Let me try the way you have described - "Click in the "address" bar where it says "This PC > Windows (C:)..." and paste the URL of your SharePoint document library"

    Thanks for the pointer. 

    Will revert you once tried.

    Thanks!


    -Vinay Pugalia
    If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
    Web : Inkey Solutions
    Blog : My Blog
    Email : Vinay Pugalia

    Monday, July 27, 2015 4:04 AM
  • Hello Seth,

    With the way you have shown, I was able to browse the excel file hosted on the share point site and select the file.

    However, after selecting the file, the Navigator popup did not show anything :( even after taking some time to process. Also, I was not shown any exception/error.

    Could you please explain what could be the reason?

    Thanks


    -Vinay Pugalia
    If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
    Web : Inkey Solutions
    Blog : My Blog
    Email : Vinay Pugalia

    Monday, July 27, 2015 9:19 AM
  • Not sure what the issue might be, I have been able to use this to get to SharePoint Online and SharePoint On-Premise. I'm running Excel 2013 64-bit.
    Monday, July 27, 2015 12:18 PM
  • Hey, today I re-tried the same and this time it worked :)

    I was able to power query the sheets from the hosted workbook.

    Thanks a lot!

    However, I have got a couple of queries now - 

    1. Can I also POWER QUERY the DATA MODELS used in the hosted excel file or it is that I can only query the worksheets?
    2. As my reporting file has POWER QUERIED the hosted file, will my report always fetch the latest data from the hosted file (assuming hosted file will be timely updated with latest data)?

    Thanks again & looking forward for your reply.


    -Vinay Pugalia
    If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
    Web : Inkey Solutions
    Blog : My Blog
    Email : Vinay Pugalia

    Tuesday, July 28, 2015 7:46 AM