locked
Linking PowerPivot model to a excel file located in SharePoint 365 RRS feed

  • Question

  • I use Office 365 and OneDrive for business to store my files, however I use multiple computers and my OneDrive for Business file paths are not always the same, for example sometimes they are on the C:\ drive, sometimes they are on a H:\ etc.

    My problem is that my PowerPivot model links to Excel files stored in OneDrive for Business to retrieve and refresh data. However each time I use a computer with a different path the files no longer refresh because they are looking in the wrong place. I'm wondering if it is possible to point towards the actual URL of the file in SharePoint online so that regardless of PC being used they are always pointing at the same place.

    I have tried doing this by editing the connection and navigating through the file explorer window to my correct OneDrive directory:

    "https://companyname-my.sharepoint.com/personal/james_companyname_onmicrosoft_com/Documents/Forms/All.aspx"

    I can see the file I want to link to here but when I try and select it I get the following error:
    "The filename, directory name or volume label syntax is incorrect"

    Is what I'm trying to do possible? Most of my data comes through MySQL statements but there is some data I can't get anywhere else.

    Thanks,

    James


    • Edited by Maracles Tuesday, December 2, 2014 10:31 AM typo error
    Tuesday, December 2, 2014 10:27 AM

Answers

All replies

  • Anyone have any thoughts on this?

    I'm having a lot of issues as everytime I use a new computer I have to repoint all myfiles.
    Monday, December 8, 2014 11:47 AM
  • Does anyone have a suggestion for Maracles?

    Thanks!


    Ed Price, Azure & Power BI Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Friday, December 12, 2014 11:58 PM
  • Maracles, is this still an issue?

    Thanks!


    Ed Price, Azure & Power BI Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Wednesday, December 31, 2014 2:35 AM
  • Hi Ed,

    Yes it is, very much so actually - just yesterday I've been somewhere different working for the holidays and using the new machine I have to again edit the links of all of the files linked to my data model because the drive letter was different for where my OneDrive for Business is saved. I will then have to re-link them again when I am back at my regular office or using my laptop.

    What I'm looking for is to be able to point my DataModel to the files via URL which will remain constant.  This used to be possible when I was using hosted SharePoint.

    For anyone suggesting I just save OneDrive For Business to the same drive on all machines, this is not possible.

    Any suggestions would be very helpful.

    Thanks,
    James

    Wednesday, December 31, 2014 9:47 AM
  • Anybody have any ideas for James?

    Thanks!


    Ed Price, Azure & Power BI Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Tuesday, January 6, 2015 6:50 PM
  • I submitted a request to get help on this. Thanks!

    Ed Price, Azure & Power BI Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Tuesday, January 6, 2015 7:01 PM
  • Here's one attempt at an answer...

    Both Power Pivot and Power Query need the absolute File location. so %username% doesn't work.

    One "workaround" that seems to work is to MAP the OneDrive URL FOLDER as a Mapped Network Drive. It works with Power Pivot out of the box, but Power Query sends a DataSourceError you have to manually change the Source in the Query to make it fly.

    FROM: Source = Excel.Workbook(Web.Contents("[SharePointURL]"))

    TO: Source =Excel.Workbook(File.Contents("Z:\ExcelFile.xlsx"))

    Then of course you have to add a mapped network drive on all your computers with the same Network Drive letter. But you don't have to change the Power Pivot Workbook. 

                  

    Anybody, please feel free to piggyback on this concept or clarify/explain anything.

    Thanks!


    Ed Price, Azure & Power BI Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!


    Thursday, January 8, 2015 8:20 PM
  • SharePoint will give you an OData feed for the data in your Excel workbooks (so long as the data is in an Excel table).

    Using OData with Excel Services REST in SharePoint 2013

    And Power Query can read that OData Feed, and supports Office 365.

    So you can upload your workbook to O365 and read it from there as an OData data source.  Power Query is even smart enough to load a paged OData response.

    EG here's a Power Query that loads data from a table (Table1) workbook (Data.xlsx) on my my site ( https://site.sharepoint.com/personal/user )

    let
        Source = OData.Feed("https://site.sharepoint.com/personal/user/_vti_bin/ExcelRest.aspx/Documents/Data.xlsx/OData/Table1")
    in
        Source

    David


    David http://blogs.msdn.com/b/dbrowne/


    Thursday, January 8, 2015 10:46 PM
  • Thank you both - I've not had a chance to try this yet and will not be able to until next week but I will report back to mark it as solved or let you know of any issues.

    Much appreciated!!!
    P.s. Is this so unsual these days with so much cloud computing? Maybe I'm making my life more difficult and there is a more appropriate way?
    Friday, January 9, 2015 4:53 PM
  • Oh,

    I also just tested again with Power Query.  You can also just download the workbook over http.  You just need the correct url.  When you navigate to the file in your browser, you get a url to display the workbook in your browser. You need to use the url of where the document acutally is. 

    If you just put the folder name in, you should get a file picker,  eg https://microsoft-my.sharepoint.com/personal/myusername/Documents

    Here's the Power Query I used:

    eg

    let
        Source = Excel.Workbook(Web.Contents("https://site.sharepoint.com/personal/myusername/Documents/Data.xlsx")),
        Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data]
    in
        Table1_Table

    David


    David http://blogs.msdn.com/b/dbrowne/


    Friday, January 9, 2015 5:19 PM
  • Hi David, thanks for this solution - should it apply to PowerPivot?  

    I felt like it was the simplest so I've tried an implementation of it however run into some problems. I actually tried to implement it by editing the existing source, clicking browse next to the 'Date Feed Url' feed. I pasted my URL file address and file explorer does successfully navigate to the SharePoint folder and show me the file I want to link to. Unfortunately I can't actually select a file, when I try and do so nothing happens.

    As far as I'm aware I can't enter in a source in the same format as you've suggested as that is exclusive to Power Query - is there a similar, advanced way of entering something similar into PowerPivot?

    I have also tried the OData method but don't seem to be able to construct the correct URL as I get a 'URL Not Found' error with what I think is the correct one. Is there a tool, or a link directly within SharePoint/Excel that generates the correct URL for the OData feed?

    Thanks,

    James

    Monday, January 12, 2015 6:07 PM
  • You can use PowerQuery to load the PowerPivot table.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Monday, January 12, 2015 9:29 PM