Power Query and SharePoint

cmcreynolds

Active Member
Joined
May 21, 2015
Messages
295
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
 
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,
 
Upvote 0

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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?
 
Last edited:
Upvote 0
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?
 
Upvote 0
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.

RefreshError.png
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top