A cleaner way to import files from a SharePoint folder with a Power Query function

[@JackOfTrades12]

This is awesome. I've been doing it without the function and it's very messy. I'm going to save and share this video with my team. This is incredibly valuable.

[@CurbalEN]

Thrilled to hear :) /Ruth

[@henriknymann9284]

You are simply a life saver... As self taught in Power BI, your videos have helped me gain enough knowledge to be able to create complex reports to be used by hundreds if not thousands..

[@CurbalEN]

Proud to be your copilot! 😊

[@dandysixties]

I keep coming back to this video because is probably the MOST USEFUL for me of all videos out there lol

[@CurbalEN]

I do it too 😂😂

[@lenzypetty9371]

Great explanation. I used the SharePoint Folder connector to connect to my Online SharePoint Folders instead of the SharePoint List Connector. As a result, I was able to start at 2:22minute mark in the video.

[@markmcc74]

absolute life saver! this has had me stumped for 2 weeks or more, thank you!

[@CurbalEN]

🥳🥳

[@mozgus79ify]

Thank you Ruth, I'm beginning with power query and PowerBI and your videos are amazing.

[@thundercatshooo8128]

This was my first time importing from a SharePoint folder. You made this look simple. Thank you soo much.

[@JeffAtkins79]

This is enormously helpful. I've watched and followed along more than once.

[@CurbalEN]

Wonderful!

[@jameschurch510]

Still using this method years later, thank you so much!

[@CurbalEN]

😃

[@frankverabarces803]

Gracias! This video has literally changed my life!

[@CurbalEN]

Un placer!!

[@faizanalimohammed9692]

Man I don't know what to say this is awesome. Thank you so much, you saved me doing a lot of steps.

[@alagator28]

Thank you so much. Great explanations and it works perfectly!

[@paulexcellence8924]

I honestly liked the way you presented this video, but also the effectiveness of this module. You are the best ......

[@CurbalEN]

Thanks!

[@MusicPancho]

April 2021 - Works for me! I had to adjust the formula in my power query when importing, had to write ", [ApiVersion = 15]" after changing Tables to Files at 2:12 :)

[@CurbalEN]

👏👏 Thanks for sharing !

[@MusicPancho]

Just figured out that actually now we can use Sharepoint Folder connection, so it automatically goes to the files.

[@alexkim7270]

Brilliant as always. I knew I can always refer to your videos when I'm stuck at work! This is gonna be my first time doing this. Excited to try that out now!

[@CurbalEN]

Thanks and you will be able to set it up in no time!

[@albertomendozaa]

A very clever and elegant solutions, love it, love your content, all of it!

[@CurbalEN]

🎉🎉

[@KevinMichaelCarroll]

This is fantastic and will save me a lot of time. I was also able to tweak the approach slightly to allow for file gathering across sub-folders by using Text.Contains and adding a FilePath parameter to the function in addition to FileName.

[@marcelavasconcelos228]

Can you share your function?

[@vinaykumar-sl3qr]

Can you share the code, please..

[@Crysteps]

Ruth, Thank You SO MUCH! I learned a fantastic trick today from You and already applied it to my own datasets. This will save a ton of time for my team. Thank You!

[@CurbalEN]

🥳🥳🥳 Music to my ears!!! /Ruth

[@Crysteps]

@@CurbalEN Ruth, is there a way to apply this formatting to several sheets in a workbook but not all of them? for example if I have a workbook with 10 sheets but I only need data from 7 of them.

[@CurbalEN]

Yes, but how depwnds on how your data looks like. Post in the power bi community with example to get help!

[@ampung1]

Thank you sooo much!! This is the 2nd times I return back to your tutorial again. Your video is my favorite and meaningful and powerful for me (and many people also :)

[@Redz85Gaming]

Thank you for your help, now i can navigate through sharepoint folders smoothly through the PowerQuery

[@Mudiaga11]

This is one of the most helpful videos I have come across. Thank you!

[@alexandreferraro3731]

OMG you are the best. I have been struggling with that for a long time. Thank you very much

[@CurbalEN]

Happy to help and enjoy the weekend :)

[@vinuthan13]

Thank you very much. Saved hell lot of time. Crystal Clear

[@CurbalEN]

Wonderful!

[@scottymac734]

Great guide to doing something that is not at all intuitive, which can can go very wrong if not done correctly (ie, as described here).

[@djohnysilva]

You are brilliant, this is great, many thanks for uploading this video and all the hard work.

[@CurbalEN]

The pleasure is all mine 😀

[@xpranax]

Thank you so much for this i loved your explanation! It saved me weeks of work

[@CurbalEN]

Music to my ears!

[@AnandAnand-ii6ef]

That was fun watching all my files getting loaded automatically

[@CurbalEN]

Enjoy!

[@carlosfernandohernandezpor6845]

Great tip! Thank you for your awesame help

[@juanquintero8661]

The only video could really help me

[@mustardetienne2240]

Thank you for your help, I was coming crazy seeking for an answer.

[@CurbalEN]

Glad it helped! /Ruth

[@AndrewClark83]

This is brilliant, thanks so much for sharing this one with us.

[@shijumatthew]

Superb... Awesome functionality. Thanks

[@CurbalEN]

Thanks!

[@bedsup]

Lovely. Thank you very much for sharing this. You are awesome.

[@ericsamson5589]

Great video, very useful and well explained

[@CurbalEN]

Thanks Eric!

[@rauljimenez5485]

As always, this is genius!!! Thank you!!

[@CurbalEN]

Thanks!

[@jpdeion8368]

Fantastic, it helped me a lot... Thank you very much for sharing :)

[@CurbalEN]

My pleasure!

[@erickelley7703]

Subscribed because of the look on your face when it was about to get cool.

[@CurbalEN]

😂

[@TheRavee05]

thank you very much its a big help please post some more videos like this

[@CurbalEN]

Will do!

[@RESUMAOAUDIOBOOKS]

Thank you so much for sharing this. Have a nice day :)

[@nikhargesumit9069]

What should I say , You are simply awesome. Thanks for sharing the knowledge. :)

[@CurbalEN]

😊, you too! /Ruth

[@ezeononuju2714]

This is just great, thank you very much for this

[@nachomiranda5151]

Thanks a lot Ruth, Believe it or not, I’ve been fighting with this all morning long. Now life will be much easier 😀 The only remaining issue is that I need to load several sheets from all the files and put them together creating several different tables. Thanks

[@CurbalEN]

I got you covered : www.google.com/url?sa=t&source=web&rct=j&url=m.youtube.com/watch%3Fv%3DPlSKlFGg-JQ&ved=2ahUKEwjrg_K7otnnAhVrw4sKHW2nB2wQwqsBMAB6BAgFEAQ&usg=AOvVaw3LB3-lKcxPaVgSZNGH9EqK ;) /Ruth

[@aneeshs8121]

Thanks very much for your help

[@lma3996]

Thank you so much! That was great. Actually, I was looking for

[@samset3890]

This is fantastic, thank you !

[@Imrannaseem818]

wow you saved my life thanks

[@QuintJer]

You are my hero! I was looking for a way to do this but using Excel in office 365 and its pretty much the same. Thanks again...

[@CurbalEN]

Yey, glad it worked!!

[@minyiau]

Thank you so much!!! you really helped me alotttt

[@monserratanahireyeshernand6553]

Can I apply more steps to my tables after this? Or I have to do it before I do all the function subjet? Thank youuu

[@badreddinemachkour3643]

thanks a lot !!! very helpful !!

[@scfo]

Super video Ruth. Very valuable. Like!! Like!! Like!!

[@CurbalEN]

🥳🥳🥳 /Ruth

[@johnbiltaji9554]

Awesome share, thank you so much!

[@giorgioiribarren]

Gracias por esta nueva alternativa

[@CurbalEN]

Un placer!

[@vishnurajasekharan619]

Pretty good. thank you!! The tab name should be the same in all the files.

[@aaronrobertcoughlan]

Just curious to know what the advantage is of this over the automatic helper queries generated by PowerBI?

[@CurbalEN]

Less messy and highly configurable ;) /Ruth

[@aaronrobertcoughlan]

@@CurbalEN Interesting, will check it out tomorrow! :) Thanks

[@hrishihatolkar1944]

Just awesome 👍

[@yitzchokfeig9373]

Thanks for your video's Ruth! I've been doing this a little differently. With your function, I assume it goes back to SharePoint when the function is invoked for each file. I remove the top few rows in the advanced editor and only leave the transformation steps. In your example, the first row would be #"Promoted Headers" and instead of referencing Data it would reference the variable. Then when you invoke the custom function, instead of feeding it the file name, you feed it the "Content" column. In short - we already have the content loaded so might as well just use the function to clean the files instead of reloading them.

[@CurbalEN]

Great tip thanks! /Ruth

[@Shamaoxuan]

Great content!

[@viviancheng6381]

Awesome! Is there a way to get the files in sub folders as well?

[@Adam_K_W]

Very well done!

[@niknurulrasyadanikzainalab4875]

awesome!! thank you very much

[@CurbalEN]

You welcome! /Ruth

[@barbaragerbert1847]

Thanks a lot for this video - great stuff. Do you have a trick how to get also the file names into the tables?

[@JegErAlan]

Awesome video, thanks! I also figured out a way to use this with SharePoint Online...although, right now it cannot be done as directly...

[@RobertoStaltari]

Thanks Ruth! I've been using this technique to ingest numerous CSV files from a SharePoint folder for years (I live off CSVs :( ). It's working fine, but the payload to access SharePoint is time-consuming so I have to move this code in a Dataflows entity before start cranking up data in PBI.

[@CurbalEN]

Yes, too many files and data will choke power query :( /Ruth

[@d.scovery8615]

How to locate a folder embedded in a team site for an organization? Can't follow video beyond 1:45.

[@martinclayton8036]

@@d.scovery8615 I had the same. My hack is to filter the Folder Path (you have to click on Transform Data). You might find your folder in the list, if not then select only one and apply the filter, then go into the Filtered Rows step and apply the correct folder location in the filter.

[@Selom-k2b]

Hi Ruth, I want to get the date the file was created so I first combine the content at the binary stage. But I can't figure out how to allow the function to work. Any ideas? The first step creates transform function and sample file. Thank you

[@ryanbinkley7348]

Do you know what to do if an error pops up saying " Access to the resource is forbidden?"

[@GreenBaron]

My files are stored in a folder within a Document Library and do not appear on the file list. How do I get it ti show files from a folder within a List ?

[@johnpauldelacruz7957]

HI, Excellent work on here. May I ask what if I want to grab someone's SharePoint folder including the sub-folder and files which has been shared to me. Additionally, I want to track the date modified or version history using power query. Is it possible? I want it to be on Excel Power Query. Thanks!

[@pascalbellerose]

I don't get the point to go down this path since Sharepoint Online now support Sharepoint Folders. ?? Or am I missing the point? Iwas looking for a solution to get directly to the folder I'm targeting instead of going through the whole site's files list (ours is huge and takes forever to load in Power BI). I'd be interested to knwo what you have to say about this issue. Good video, thanks!

[@azharshaik21]

Thank you, Can you also make a video on how to export the data from power query (excel) to share point ?

[@azrihazwanazahar2863]

Hi, I can't import my files that was in format PowerPoint (pptx). Is there any method to import the pptx file in Sharepoint to excel?

[@dianajimenez2557]

Hello! Thank you so much for the video. It works. However, I have a report published and it is automatically refresh but it seems to have problems refreshing the Sara form the folder because it is attached to my user. Is there any way to fix this ?

[@ismafoot11]

Is there a way to do import excel files within a folder which is inside another folder in the Documents folder using that connector ? I wasn't able to do so . We're using an organizational sharepoint account

[@johnfought7541]

This is definitely cleaner, but does it increase the refresh and load times as opposed to keeping all the steps?

[@razelllamanzares]

Question is this a form of automation? In other words, if a user were to change/add to the SharePoint Excel file will a Bower Bi scheduled refresh to obtain the new values?

[@CurbalEN]

Yes :)

[@jennifersanchez1522]

How would I go about changing the code for the function in advanced editor if my sheet names are different in each file? Thank you!

[@Checkmate234]

Thank you so much, very insightful, i would just like to ask - i've been having immense difficulties scheduling a refresh on the PBI service using Sharepoint folders as a source. Have you encountered any issues? The error is mostly related to credentials , however, I added a service account to the model and datasources, including the Sharepoint location. So I have no idea what it could be.

[@CurbalEN]

That is weird... I hace built many models using sharepoint as a source and the only time I experience issues was when the data model got too big or the data load to heavy. Hope this helps! /Ruth

[@scfo]

Hi Ruan, I had the same issues last week with data not refreshing from Sharepoint in PBI Service? Are you appending data from a different source to to/from your sharepoint file? Are you located in South Africa? I used dataflows to clean historical accounting data via sharepoint and appended the old data to a new cloud accounting software and Power BI then put up a fuss with the automatic refresh. Is was credential issues on Power BI Services that you had to change for sharepoint to public BEFORE your FIRST refresh. If you change the credentials after the initial refresh. Sent me an email and hopefully we can sort out your issue. [e-mail]

[@williamchai3218]

i cant find sharepoint online folder from the list

[@divya7555]

Hi Curbal, Incremental refresh is possible if we combine multiple excel sheets with this cleaner way? Kindly advice. And how fast we can refresh this dataset? I have 20 excel sheets and it's taking more than 2 hours for a full load ...

[@johnwilkins2992]

This video was incredibly helpful. I need to do the exact same thing but for all files in a folder and all of its sub-folders. Is there a way to do this with a similar approach.

[@srinivasanviswanathan6534]

Hi Ruth - what if you only have access to certain subfolders of the Sharepoint site, and not the "root" access? In that case, can you enter in a URL to a subfolder for it to pull from there?

[@mikedbman]

I have found that if you reference the entire folder AND file you can refer to the entire address and not get those dreaded errors. I then don't use the SharePoint list, just open a SharePoint file. I don't know if this will help in your situation but it did for me. I was hoping for a better answer to your question as I have the same issues but nobody gave one.

[@paspuggie48]

I love this Ruth...right up my street! I'm also keen to show you how I use PBI and SharePoint to access my libraries. Let's just say I work at an organisation with over 12,000 people and what I did was really unique. To some it may be old school but to me it was innovative. How do I share? Id like your opinion. Best, Paul

[@CurbalEN]

Hi Paul, Contact me here: curbal.com/contact and I get back to you :) /Ruth

[@paspuggie48]

Brill. Thank you 😎

[@d.scovery8615]

How to locate a folder embedded in a team site for an organization? Can't follow video beyond 1:45.

[@Ganja1974]

cool! thank you!

[@CurbalEN]

💃

[@zanferris1]

I got it to work and it's an excellent tool to know for Power BI. I do have a question. If I'm pulling Excel files into a SharePoint folder (365) and the files are similar in everyway EXCEPT the Sheet Name, how can I rename the Sheet in Power BI before the Function runs? If the second file tries to run, and the Sheet name varies, it errors out. Thanks!

[@jancarlosascencio9870]

Hello ruth, I have a query, I want to be able to schedule scheduled updates for my excel online with sharepoint, my model was built from power bi desktop, but in pbo service it does not allow me to add a gateway I have authentication errors

[@CurbalEN]

You can ctrl+space to call intellisense. /Ruth

[@jancarlosascencio9870]

@@CurbalEN Hello Ruth i dont understand

[@houstonfirstitdepartment9966]

This is really great. Would you share whether you are in a Premium Capacity or not? My queries are taking a very long time to load, but it could be because we aren't.

[@CurbalEN]

Not me either. Always pro, unless I say otherwise.

[@houstonfirstitdepartment9966]

@@CurbalEN Thanks for the info! Any suggestions on how I can speed things up?

[@JenniferAYoung]

Thanks so much for this video, it's got me so close to what I need! My question is...please can you tell me, is it easy to add a new column which shows which file name the data has come from? I need to be able to use this for a reconciliation of multiple files and so it's important for me to know which file each set of data has come from. Thank you again, this is a fantastic video!

[@bedsup]

Hi. When you initially create your connection (i used SharePoint Folder). The name of the file appears when you expand data. You can include the file name a long with content as part of the function. (Hope that makes sense)

[@CurbalEN]

Awesome, thanks!

[@JenniferAYoung]

bedsup thank you so much! That really helps.

[@MrPeterbuma]

@@bedsup Unfortunately this is where I am but am a bit unsure do you have a resource I could reference?

[@bedsup]

@@MrPeterbuma hi Peter. I don't have access to my laptop now until. Once I'm back, I can check this provide you with the assistance. Thanks

[@charliebacon82]

Hi Ruth, this looks nicer than the awful helper files that are created when using the 'From Sharepoint Folder'. I wonder if mapping the sharepoint site to a drive and using the standard 'From Folder' option creates any improvement in performance?

[@CurbalEN]

I don't think so but File.Contents seems to be faster. Try it!

[@joebateman9668]

I would like to be able to connect directly to files in SharePoint online but every time I try I and "Access is forbidden" warning. Is this is an administration issue and they way we have our SharePoint sites configured?

[@greghodgkinson9580]

Ruth, I've followed along exactly to what you're showing and I get this error: Unable to connect We encountered an error while trying to connect. Details: "Access to the resource is forbidden." Any ideas or suggestions?

[@arulanand12]

Hey Ruth, Thank you very much for this video. I need one help from you. I am not able to get some folders from s Sharepoint online site where as I can get other folders from the same site. Have you ever faced/heard of this issue? Any help please?

[@CurbalEN]

I am afraid not. Maybe an access rights issue? /Ruth

[@arulanand12]

@@CurbalEN I am the owner of the site. So I do not think access rights is the issue here.

[@arulanand12]

@@CurbalEN When I tried to connect a single excel file, it threw an error message saying that the file path is exceeding 256 characters windows limit. Now I can understand the issue. I will see what I can do to reduce the characters either by renaming the files or folders. Thanks

[@TD-mp5nf]

Hi, thanks a lot for your video. Would it be possible to explain how to CHANGE the source to SharePoint Folder or File or OneDrive? Having tried many times so far, we got various errors. Our reports have been built based on local files. Now we uploaded the file to SharePoint but when trying to change the Data Source in PowerBI Desktop my credentials fail. We just wanted to know if what we're trying to do is something doable regardless of permission issues. Thanks!

[@monserratanahireyeshernand6553]

Could you resolve this or found any solution?

[@ilariabracchi7322]

Hi , thanks a lot for sharing this. I get the following error "The key didn't match any rows in the table. Details: Key=... Name=Name" Any ideas about the cause?

[@luisfernandomacedo1451]

I got an error message for folder path column when I tried replicating the steps. what did it go wrong?

[@jorritrouwmaat432]

Thanks for the tutorial. This is actually the method I am using to get some files from a SharePoint folder. However, the SharePoint is steadily getting bigger (over 300k files). Therefore, it is becoming unworkable. Is there an even better solution to point to a specific folder and get mulptiple excel files without loading all the files on the sharepoint in the first place? If not, I will move to a local approach.

[@murthysydney4372]

Thats a cool way, but if there are folders without any files in the folders dosent pick the folder or the path. Is there any solution for this. Using Sharepoint.List or Sharepoint.Table

[@jpdeion8368]

Hello, this function worked very well until someone put a new Excel file with a different name and a different structure in the same SharePoint folder... Do you know how I can exclude this specific file from the refresh of the function. Thank you :)

[@CurbalEN]

You can filter it out with contains or does not contain for example?

[@ybarsabal]

Why not just use the Combine Files option? When you've filtered to the folder, there's an option in the Content column that does this. Is your method more performant?

[@CurbalEN]

I would say it is a cleaner way and easier to understand, but that is subjective ;)

[@ehdiwownganga2687]

is it applicable to a folder with existing mutiples files? im stucked on that process. thanks for sharing this one.

[@melissagenthner2705]

This is exactly what I want to do but its not finding my sites since I have embedded folders and files. Similar another comment below but I don't see a response. Any suggestions?