Power Query and SharePoint

[cmcreynolds]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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?