# Power Query and SharePoint



## cmcreynolds (Jun 29, 2015)

Hello, I'm asking a more hypothetical question regarding Power Query and where to store files. My (very small) company is looking to do away with their network drives and move storage to MS SharePoint. I have access to SharePoint and they've created a path in my "file explorer." We also have OneDrive available, but we do not have the PowerBI product. 

So, I, the analyst, have been asked to build reports from several sources and having fallen in love with PQ and PowerPivot, I would like to utilize those before any other Add-In. 

My question is where to store my files. I completely get how PQ and PP work with network connections, but I'm confused as to how to create connections with SharePoint. *Since there is a path to my sites on my file explorer, can I just use those (i.e. "From File") options or is it better to use "From Web"? *

I'm still wrapping my head around what SharePoint is. What I do know is OneDrive will not allow you to create paths on your machine from another person's shared folder (my main issue). I just want to utilize SharePoint and PowerQuery correctly and most effectively. 

Thank you.

Cliff


----------



## scottsen (Jun 30, 2015)

"store my files" -- are you talking about the final xlsx file with the powerpivot model and reports?  However you can get that up on sharepoint is peachy.  Doesn't matter.

If you are talking about the source files (that PQ is pulling from), ... then it kinda matters, but only if you are worried about auto-refresh scenarios.


----------



## cmcreynolds (Jul 1, 2015)

Sorry, that was kind of vague, wasn't it? 

I meant the source files - where should they be housed?


----------



## Matt Allington (Jul 2, 2015)

You can create a Mapped drive on your PC which is essentially a copy of a SharePoint document library. You can then point power query to the files on your PC and operate as normal  using power query 

Using SharePoint Document Library as a Network Drive | Mr. Office 365


----------



## miguel.escobar (Jul 2, 2015)

if you're using SharePoint 2013 or SharePoint online then you can use this:
https://support.office.com/en-sg/ar...026-b3b7-bccc9cf563e9?ui=en-US&rs=en-SG&ad=SG

and simply query the files from your SharePoint folder (and reading the binaries) instead of downloading the files locally.

Note that the speed and performance may vary depending on your SharePoint server.


----------



## TimRodman (Jul 3, 2015)

Also, you might want to take a look at Power Update if you want to automatically refresh your PQ/PP solution.


----------



## cmcreynolds (Jul 6, 2015)

Thank you very much - 

First, TimRodman - I have set each individual connection to refresh whenever the file is opened (online or on the desktop) - and it seems to work without an additional app.

MattAllington - I do have my Sharepoint lists mapped on my PC, I just didn't know if that mapping would work (efficiently?) with Excel Online.

Miguel.escobar - do I have to utilize the SharePoint.Contents if it's mapped to my PC.


So, it sounds to me like just having it mapped to my PC works best (and easiest in PQ, from what I saw). However, when a coworker shares a SharePoint site with me and I have edit permission, will that show up where Sharepoint is mapped?

Like I said in my original post, I just want to use both products most efficiently. Also, I'd be totally cool with having both source files and reports on Sharepoint - does that matter in this conversation?

Cliff


----------



## miguel.escobar (Jul 6, 2015)

you don't need anything in your PC if you use SharePoint.Contents. The good thing about this is that you can share this query with other colleagues and that'll work for everyone whereas the local file path would be something that might only work for you


----------



## cmcreynolds (Jul 6, 2015)

Miguel - thank you. Would I be able to read the entire "site" like a folder in PQ? I plan on adding a lot of files to the site each week and would rather not have to set up a separate query for each file added. 

Is that how SharePoint.Contents works? 

Do I access SharePoint.Contents through PQ's "From Other Sources>From SharePoint Lists"? I thought the list was different than the documents. When I use the "From SharePoint Lists" I do not see my workbooks, just

ComposedLooks
Microfeed
UserInformationList


----------



## miguel.escobar (Jul 6, 2015)

This is how it looks like:






It is pretty similar to the "From Folder" experience


----------



## cmcreynolds (Jun 29, 2015)

Hello, I'm asking a more hypothetical question regarding Power Query and where to store files. My (very small) company is looking to do away with their network drives and move storage to MS SharePoint. I have access to SharePoint and they've created a path in my "file explorer." We also have OneDrive available, but we do not have the PowerBI product. 

So, I, the analyst, have been asked to build reports from several sources and having fallen in love with PQ and PowerPivot, I would like to utilize those before any other Add-In. 

My question is where to store my files. I completely get how PQ and PP work with network connections, but I'm confused as to how to create connections with SharePoint. *Since there is a path to my sites on my file explorer, can I just use those (i.e. "From File") options or is it better to use "From Web"? *

I'm still wrapping my head around what SharePoint is. What I do know is OneDrive will not allow you to create paths on your machine from another person's shared folder (my main issue). I just want to utilize SharePoint and PowerQuery correctly and most effectively. 

Thank you.

Cliff


----------



## TimRodman (Jul 6, 2015)

@cmcreynolds - If refreshing on open works for you, I would stick with it. If you find that refreshing is taking a while and your users are complaining, then you might want to look at *Power Update*. That's the situation I eventually found myself in (*click here*). Power Update can point to a regular folder or a SharePoint folder and do the refresh for you. Then users just open the Excel file knowing that the data is as current as the last refresh.

Regarding your question about Power Query -> From Other Sources -> From SharePoint List (pictured below), that will allow you to connect to SharePoint List data and it maps to the SharePoint.Tables Power Query Function. Miguel's picture is showing how to connect to SharePoint Library data and is using the SharePoint.Contents Power Query Function. I don't think there is a nice menu option for SharePoint.Contents. Miguel, please correct me if I'm wrong.


----------



## miguel.escobar (Jul 6, 2015)

SharePoint.Contents is one of those hidden gems Power Query has


----------



## TimRodman (Jul 7, 2015)

@Miguel - I guess I haven't had to use SharePoint.Contents yet because I try to source my data from a Database or a SharePoint list since they provide more structure. But I can see how many people only have Excel as a data storage option so they need SharePoint.Contents. It would be nice to have it on the menu though with a prompt for Site Address, Library Name, Excel File Name, and Excel Table Name. Maybe not that last one though since people don't always use Tables.

@cmcreynolds - To use SharePoint.Contents, just do the following:

Go to Power Query -> Get External Data -> From Other Sources -> Blank Query (don't be afraid, this is the real "power" part of Power Query)
Paste the following in the formula bar (which I stole from Miguel's screenshot). Replace MySharePointSite with the URL to your SharePoint site and MyLibrary with the name of your SharePoint library that has the Excel files in it (might be called "Documents"):
=SharePoint.Contents("MySharePointSite"){[Name="MyLibrary"]}[Content]
You will now see a list of Excel files. Click the word "Binary" next to the Excel file that you want to access
You will now see a list of Worksheets, Tables, and Named Ranges. Click the word "Table" next to the one that you want to access
You should now see your data


----------



## ralliartur (Jul 7, 2015)

There are more options to do it.

1. Open sharepoint site, go to Library -> open with explorer
2. Windows Explorer window will open -> copy full address from navigation bar (it should be like https://sp_server/sp_site_address)
3. Remove "https:" from string, and change / to \ -> in the end the path will look like \\sp_server\sp_site_address (you can use notepad for this operation)
4. Now you can paste new link to "From folder" option in Power Query


----------



## TimRodman (Jul 7, 2015)

Nice!


----------



## cmcreynolds (Jul 7, 2015)

Okay - so would utilizing SharePoint.Contents be "better" (I know that's  quite vague, too) than putting the path as ralliartur suggested?

TimRodman  - Power Update looks pretty awesome! I'm not sure that my company (I'm  the only person creating reports - we're that small) would need  something that powerful - I'm just pulling maybe 5000 records at a time  from SalesForce or our own CRM. BUT, I will definitely look into it if  we become bigger and require such pulls. Oh - and thanks for your  instructions! 

OH - and I know this is a SharePoint question - but do I still have to worry about putting spaces in the names of "sites"? Is that still a "thing"?


----------



## TimRodman (Jul 7, 2015)

Maybe you could try the spaces and let us know if they work.


----------



## cmcreynolds (Jul 7, 2015)

Seems to work fine with spaces (but the URL copies over with % instead of spaces, which is per usual, I think).

NEW SNAG - it's asking me for credentials, then telling me I'm not authorized. I built the sites and I'm the only one accessing them! In the URL where it's asking me permission it contains a specific document - surely I don't have to give myself permission for each document, do I?


----------



## cmcreynolds (Jul 7, 2015)

So, I removed the "https://"  and it let me access the documents. 

NOW - after creating a new column "Excel.Workbook([Content])", it gives me an error for the first document and I can't figure out why. Is it because of headers or something?


----------



## cmcreynolds (Jul 7, 2015)

That first document is constructed just like the others, but still gives me an error instead of table (the error is "DataFormat.Error:....not a valid Excel document" - but it is! *.xlsx; no macros; no connections; no data model; no pivot tables; just a table with 13 rows of data. Very frustrating.


----------



## cmcreynolds (Jun 29, 2015)

Hello, I'm asking a more hypothetical question regarding Power Query and where to store files. My (very small) company is looking to do away with their network drives and move storage to MS SharePoint. I have access to SharePoint and they've created a path in my "file explorer." We also have OneDrive available, but we do not have the PowerBI product. 

So, I, the analyst, have been asked to build reports from several sources and having fallen in love with PQ and PowerPivot, I would like to utilize those before any other Add-In. 

My question is where to store my files. I completely get how PQ and PP work with network connections, but I'm confused as to how to create connections with SharePoint. *Since there is a path to my sites on my file explorer, can I just use those (i.e. "From File") options or is it better to use "From Web"? *

I'm still wrapping my head around what SharePoint is. What I do know is OneDrive will not allow you to create paths on your machine from another person's shared folder (my main issue). I just want to utilize SharePoint and PowerQuery correctly and most effectively. 

Thank you.

Cliff


----------



## miguel.escobar (Jul 7, 2015)

hey guys,

Sorry for being MIA. I think you guys figured out how to make the SharePoint.Contents work already. All  you need is a text string of the url for your sharepoint site...that's it.

For debugging that file, try hitting in the Binary file and then reading that binary file as an Excel File.....try then downloading the file and reading that specific file from power query as an excel file and check if you get the same result. 

If you keep getting the same issues, then try promoting the first row as headers using Excel.Workbook( [Content], true) and see if that helps a bit.

Best,


----------



## cmcreynolds (Jul 8, 2015)

Okay - get this! - so, right now I'm pulling from about 9 files (there will be more later this year, for sure) - the one that kept returning the error is a new client and it will be replaced with "better" data anyway. SO...I simply filtered "remove errors" thinking I'll deal with it later. BUT, when I looked at the Data Model - that data had been loaded with everything else. So, maybe it's reading it as an error but loading it down the pipeline anyway? Freaky

Refreshing is an issue now since the June 23rd update to Power Query. The reports viewed in Excel Online through SharePoint won't refresh where they were before. 

Is Power Update the only workaround? How exactly does it work - it schedules the refreshment (ha) and then saves so the next time someone opens the file the data will be new? If I get Power Update, what should I do with the "Refresh Every Time the Document Opens"? untick it?


----------



## cmcreynolds (Jul 8, 2015)

I came across this article and I wonder if this applies to my situation and would help with data refresh:

https://technet.microsoft.com/en-us/library/jj218792.aspx

I just have the regular Excel PowerPivot add-in, do I need this other piece? How do I convince the IT department to install it for me?


----------



## TimRodman (Jul 8, 2015)

Just to clarify, are you refreshing within the Excel Online web-based client or within regular Desktop Excel? Can you post a screenshot of the error you are getting?


----------



## cmcreynolds (Jul 8, 2015)

I had set up the refresh to happen whenever the file is opened and up until Monday, it was working. Now, it doesn't. (boo...hiss)

I've had coworkers open it in both the Online and Desktop and it gives refresh errors to both.


----------



## TimRodman (Jul 8, 2015)

Weird, I see what you mean that it seems like a bug with Power Query. Did you report the bug using the frown face?

FYI, the picture didn't come through. You can upload it to a site like Imgur and then link to it from there.


----------



## cmcreynolds (Jul 8, 2015)

Imgur

Never used imgur before...I hope that works


----------



## TimRodman (Jul 8, 2015)

That worked. You sent a frown face?


----------



## cmcreynolds (Jul 9, 2015)

Well, that's the thing, since the refresh works for me under my credentials, I can't send the frown face, a coworker would have to. I asked our IT team to look into the PowerPivot for SharePoint - but not sure how far they're getting with it.


----------



## miguel.escobar (Jul 10, 2015)

hey cmcreynolds,

I'm not completely following your problem. If I understood correctly, you're trying to refresh your Excel file (with Power Query and Power Pivot in it) in SharePoint and it doesn't refresh.
-=----I'm not sure what version of SharePoint you're using, but if you're using a local instance of SharePoint then Power Query connections won't refresh. Power Query will only refresh on Power BI,

In the event that your colleagues try to refresh a file that has Power Pivot and Power Query, they'd need both of those add ins if they use Excel 2010 and Power Query installed if they use Excel 2013 since its still not integrated with Excel 2013.


----------



## cmcreynolds (Jun 29, 2015)

Hello, I'm asking a more hypothetical question regarding Power Query and where to store files. My (very small) company is looking to do away with their network drives and move storage to MS SharePoint. I have access to SharePoint and they've created a path in my "file explorer." We also have OneDrive available, but we do not have the PowerBI product. 

So, I, the analyst, have been asked to build reports from several sources and having fallen in love with PQ and PowerPivot, I would like to utilize those before any other Add-In. 

My question is where to store my files. I completely get how PQ and PP work with network connections, but I'm confused as to how to create connections with SharePoint. *Since there is a path to my sites on my file explorer, can I just use those (i.e. "From File") options or is it better to use "From Web"? *

I'm still wrapping my head around what SharePoint is. What I do know is OneDrive will not allow you to create paths on your machine from another person's shared folder (my main issue). I just want to utilize SharePoint and PowerQuery correctly and most effectively. 

Thank you.

Cliff


----------



## cmcreynolds (Jul 10, 2015)

Miguel - I came across something called "PowerPivot for SharePoint 2013"  that (I assume) is an add-in specifically for the SharePoint server. 

BUT  - yes, you labeled my problem exactly. Everyone has Excel 2013, but  what I thought I was creating was a report (with PQ and PP) stored on  SharePoint and that they'd be able to view through their browsers. They  could access these files two weeks ago with no problem. But as of last  week, they started getting errors stating the data in these files can't  refresh because it was trying to pull from external sources. I have one  simple file using PQ and PP that pulls from a local source - they can  open them with no errors. So, that confuses me as to if the problem is  with PQ or SharePoint?

When I searched the information  superhighway, there were solutions (see links below), but dealing  directly with the SharePoint servers. We are using Office 365, so I  suppose we have SharePoint 2013 or an equivalent? 

One solution? 

SharePoint error External Data Refresh Failed:We are unable to refresh one or more data connections in this workbook.The following connections failed to refresh

Another?

https://technet.microsoft.com/en-us/library/jj218792.aspx


Please  know I don't claim to completely understand the workings of anything.  So, feel free to clarify my situation - i.e. if I need to have reports  in SharePoint but no external connections (SalesForce) or whatever. I am  completely willing to adjust to the best/most efficient setup. 

Thanks again


----------



## cmcreynolds (Jul 10, 2015)

Recap:

I have source and report Excel documents on SharePoint. I can view any and all of them without incident. My coworkers with whom I'm trying to share the files receive an "External Data Refresh" error whenever they try to open these documents either in Excel Online or in the Desktop version. After searching the web, I discovered this is a known issue - that PQ doesn't seem to work with SharePoint when there are external data sources (btw, my external source is SalesForce). So, I'm not sure the solution.


----------



## TimRodman (Jul 10, 2015)

The data refresh options are very confusing. Power Query is not the same as a regular Excel data connection. What you can refresh in native Excel is not the same what as what you can refresh in Excel Online. What you can do in Excel Online depends on what version of SharePoint and what add-ons you are running. Even if you are using Office 365 SharePoint, what you can do in Excel Online depends on what version of Office 365 you are using. Microsoft is trying to simplify all of this under PowerBI.com which will go live on July 24th, but we'll have to wait and see if things really do get simpler. So far, they do look very promising.


----------



## Matt Allington (Jul 10, 2015)

Cmreynolds

if you have Power Query in your workbooks, you can use Power Update to refresh locally and post direct into SharePoint. Power Update Software -.  The software is $500 but I think it is a bargain for what it does.


----------



## miguel.escobar (Jul 11, 2015)

cmreynolds,

For your specific situation, I'd suggest going the Power Update route as well. Your company already bought SharePoint....might as well put it to good use!


----------



## cmcreynolds (Jul 13, 2015)

So, Power Update or wait until the PowerBI goes live? Is this just an "upgrade" (term used loosely there) to the current PowerBI? And doesn't PowerBI cost extra, too? 

Again, it's ANOTHER "thing" I have to convince IT to not roll their eyes at me when I say "our company needs this." *sigh*


----------



## TimRodman (Jul 13, 2015)

Power Update is free for one Excel file to refresh and $500 for unlimited Excel files.

Power BI pricing can be found here: click here


----------



## cmcreynolds (Jul 13, 2015)

My IT just sent me the following link - but it assumes PP for SharePoint is installed and enabled. Maybe this helps? Is it a workaround that allows us not to worry about Power update or Power BI?

https://msdn.microsoft.com/en-us/library/gg413467(v=sql.110).aspx


----------



## TimRodman (Jul 13, 2015)

They can go down that path if they want, but it's not a simple installation. I think that there is a lot of "black magic" involved based on everything that I've read from those who have traveled the path.

This is why Power Update and Power BI have become popular. You are basically outsourcing the IT complexity.


----------



## cmcreynolds (Jul 13, 2015)

I wonder with Power BI - it being billable per user per month - is it billable for ME only? or everyone needing to view the report, you think?


----------



## cmcreynolds (Jun 29, 2015)

Hello, I'm asking a more hypothetical question regarding Power Query and where to store files. My (very small) company is looking to do away with their network drives and move storage to MS SharePoint. I have access to SharePoint and they've created a path in my "file explorer." We also have OneDrive available, but we do not have the PowerBI product. 

So, I, the analyst, have been asked to build reports from several sources and having fallen in love with PQ and PowerPivot, I would like to utilize those before any other Add-In. 

My question is where to store my files. I completely get how PQ and PP work with network connections, but I'm confused as to how to create connections with SharePoint. *Since there is a path to my sites on my file explorer, can I just use those (i.e. "From File") options or is it better to use "From Web"? *

I'm still wrapping my head around what SharePoint is. What I do know is OneDrive will not allow you to create paths on your machine from another person's shared folder (my main issue). I just want to utilize SharePoint and PowerQuery correctly and most effectively. 

Thank you.

Cliff


----------



## TimRodman (Jul 14, 2015)

Everyone needing to view the report. That's why Power Update is typically the cheaper option since it takes care of the refresh and users only need Excel to view the reports.

You can even publish the reports to PDF or PNG so the user doesn't even need Excel. The downside is that the reports aren't interactive.


----------



## cmcreynolds (Jul 14, 2015)

Noted. 

Guess what (didn't?) happened yesterday - colleagues opened the reports in Excel Online and didn't get an error. The only change I made was unchecking the "Refresh data when opening the file" option in "Connections". I understand that the report they are viewing isn't up-to-minute, but at least they can see the last time I saved the file. Now...back to the original idea of refreshing without good ol' Cliff doing it...  

Power Update, Power BI, and PowerPivot for SharePoint. I will look into those.  

Thank you all for your help.


----------



## TimRodman (Jul 14, 2015)

Yes! Excel Online is the way to go. It works on mobile too. You just need an automated way to refresh the data. That's where Power Update is your friend. It's a very lightweight program, it's free for one workbook, and it's super simple to setup and use (no IT help needed). I'd encourage you to give it a try (click here).


----------



## cmcreynolds (Jul 15, 2015)

And it would be payable for "my" use - in other words, not per "users" who view the file. It's basically a way for me to schedule refreshes and it doesn't care who views the actual files. (right?)


----------



## TimRodman (Jul 15, 2015)

That's right. When they open the workbook (in Excel or Excel Online), the data has already been refreshed so it's just regular Excel at that point.

You could accomplish the same thing without Power Update by manually refreshing the data in your reports every morning. If it doesn't take you very long, that might work just fine.


----------



## cmcreynolds (Jul 16, 2015)

Well, right now - we're looking at maybe 5 reports. BUT, we are taking on a lot of new clients which will require more reporting. So, $500 maybe worth it - over Power BI, that is.


----------



## cmcreynolds (Jul 17, 2015)

I now have a trial subscription to Power BI...so we'll see  (Their decision, not mine)


----------



## TimRodman (Jul 17, 2015)

Good move. I'm sure that you won't regret it. July 24th is the key date to watch. For more info, click here.


----------



## cmcreynolds (Jul 20, 2015)

I can't interpret what "general availability" means - free with Office 365 or some other subscription? Or just that now everyone can buy a subscription. lol gotta love marketing.


----------



## TimRodman (Jul 20, 2015)

It means that it will no longer be in preview (or beta). It will continue to be free for 1GB of data/user. You only pay if you go beyond 1GB.


----------



## cmcreynolds (Jun 29, 2015)

Hello, I'm asking a more hypothetical question regarding Power Query and where to store files. My (very small) company is looking to do away with their network drives and move storage to MS SharePoint. I have access to SharePoint and they've created a path in my "file explorer." We also have OneDrive available, but we do not have the PowerBI product. 

So, I, the analyst, have been asked to build reports from several sources and having fallen in love with PQ and PowerPivot, I would like to utilize those before any other Add-In. 

My question is where to store my files. I completely get how PQ and PP work with network connections, but I'm confused as to how to create connections with SharePoint. *Since there is a path to my sites on my file explorer, can I just use those (i.e. "From File") options or is it better to use "From Web"? *

I'm still wrapping my head around what SharePoint is. What I do know is OneDrive will not allow you to create paths on your machine from another person's shared folder (my main issue). I just want to utilize SharePoint and PowerQuery correctly and most effectively. 

Thank you.

Cliff


----------



## cmcreynolds (Jul 20, 2015)

wow - so, if someone looks at a large report more than once per day, does that mean they get charged more? Or is it the refresh rate?


----------



## TimRodman (Jul 20, 2015)

Take a look at this (click here). It looks to me like it's the total data storage capacity, not how much data flows through.

I think of it like the amount of storage space that you have on your cell phone, not the amount of data that you use each month with the cell phone company.


----------



## cmcreynolds (Jul 21, 2015)

It would also allow me to save my queries separately instead of me copying the same file and re-naming it - seems really important as our company grows.


----------



## cmcreynolds (Aug 10, 2015)

@TimRodman 

In Excel's Connections, if I select the data to refresh, say every 60 minutes, will the refreshed data show up regardless who opens it? In other words, in SharePoint, would someone who has "can view" permission be able to see the refreshed data?


----------



## TimRodman (Aug 10, 2015)

That will only apply if you actually have the Excel file open. It's not related to SharePoint as far as I know. It should apply to "view" permissions too, but I haven't actually tried it.


----------



## cmcreynolds (Oct 27, 2015)

@TimRodman

Well, after having PowerBI a couple of months and having MANY runarounds, come to find out you cannot simply automatically refresh an Excel workbook. It refreshes the connection between the workbook and PowerBI, but not the connections between the workbook and datasources (like Salesforce). 

And since our Sharepoint (something about farm?) doesn't support "PowerPivot for Sharepoint", I will try to convince our IT to get Power Update. 

Lots of man hours lost to trying to get PowerBI to do something it wasn't meant to do, unfortunately. *SIGH*


----------

