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

4 жыл бұрын

Thrilled to hear :) /Ruth

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

@CurbalEN

3 ай бұрын

😃

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

3 жыл бұрын

Proud to be your copilot! 😊

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

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.

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

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 :)

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

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

3 жыл бұрын

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

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

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

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).

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

@CurbalEN

3 жыл бұрын

🥳🥳

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

@CurbalEN

3 жыл бұрын

I do it too 😂😂

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

@CurbalEN

2 жыл бұрын

Wonderful!

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

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

@CurbalEN

2 жыл бұрын

Thanks!

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

Gracias! This video has literally changed my life!

@CurbalEN

2 жыл бұрын

Un placer!!

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

3 жыл бұрын

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

@Crysteps

3 жыл бұрын

@@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

3 жыл бұрын

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

Very useful video! Thank you!

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

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

2 жыл бұрын

Can you share your function?

@vinaykumar-sl3qr

9 ай бұрын

Can you share the code, please..

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

@CurbalEN

3 жыл бұрын

Music to my ears!

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

@CurbalEN

2 жыл бұрын

🎉🎉

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

@CurbalEN

4 жыл бұрын

Glad it helped! /Ruth

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

4 жыл бұрын

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

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

This is fantastic, thank you !

Great tip! Thank you for your awesame help

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

@CurbalEN

3 жыл бұрын

Thanks!

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

@CurbalEN

Жыл бұрын

Wonderful!

This is just great, thank you very much for this

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

@CurbalEN

3 жыл бұрын

The pleasure is all mine 😀

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

@CurbalEN

2 жыл бұрын

Happy to help and enjoy the weekend :)

The only video could really help me

thanks a lot !!! very helpful !!

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

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

Thanks very much for your help

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

3 жыл бұрын

Yey, glad it worked!!

Superb... Awesome functionality. Thanks

@CurbalEN

3 жыл бұрын

Thanks!

Awesome share, thank you so much!

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

@CurbalEN

4 жыл бұрын

😊, you too! /Ruth

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

@CurbalEN

2 жыл бұрын

😂

That was fun watching all my files getting loaded automatically

@CurbalEN

2 жыл бұрын

Enjoy!

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

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

@CurbalEN

4 жыл бұрын

Will do!

Very well done!

Great video, very useful and well explained

@CurbalEN

3 жыл бұрын

Thanks Eric!

Great content!

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

@CurbalEN

2 жыл бұрын

My pleasure!

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

3 жыл бұрын

👏👏 Thanks for sharing !

@MusicPancho

3 жыл бұрын

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

wow you saved my life thanks

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

Just awesome 👍

awesome!! thank you very much

@CurbalEN

4 жыл бұрын

You welcome! /Ruth

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

@CurbalEN

4 жыл бұрын

🥳🥳🥳 /Ruth

Love it!

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

4 жыл бұрын

Great tip thanks! /Ruth

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.

cool! thank you!

@CurbalEN

3 жыл бұрын

💃

Gracias por esta nueva alternativa

@CurbalEN

4 жыл бұрын

Un placer!

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!

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

4 жыл бұрын

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

@d.scovery8615

4 жыл бұрын

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

@martinclayton8036

4 жыл бұрын

@@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.

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

THE GOAT!!!

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

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

Amazing really :)

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

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 ?

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!

does anyone know how to get rid of the that's in the content of some cells pulled in from sharepoint?

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!

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 ...

hi, i tried to create a function which is based on a typical accounting file that has, among others, 12 columns for different months of the year for me subsequently reuse the function repeatedly for all other years. note: the 1st row containing the months are promoted as headers & subsequenly, i unpivoted on the months columns. however, i have a problem when i tried adding new file(s) with an error message stating that it (the function) couldn't find/use the previous row headers (e.g. 01/06/2011, 01/07/2011) since the subsequent new file has different column names for its months e.g. 01/06/2012, 01/07/2012 for the new file(s). any solution, pls?

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

4 жыл бұрын

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

4 жыл бұрын

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. adriaan.jacobs@smartcfo.co.za

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

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!

When I get to invoke custom function with my two tables, instead of seeing Table that you can click on It just says Imported Excel (None clickable). This means that i cant get any further with this. Has anyone got an idea of what I'm doing wrong

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.

You are Rock star :)>-

@CurbalEN

4 жыл бұрын

😊

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?

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

3 жыл бұрын

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

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

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

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?

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?

very useful, thank you so much! Is it possible to replicate it also for subfolders? After creating dedicated dataset, is it possible then to append each other?

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

3 жыл бұрын

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

3 жыл бұрын

Awesome, thanks!

@JenniferAYoung

3 жыл бұрын

bedsup thank you so much! That really helps.

@MrPeterbuma

3 жыл бұрын

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

@bedsup

3 жыл бұрын

@@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

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

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

Curbal is the only 3/4 chanels I have subscribed in KZread. Your videos are great. when I do the same I do not get "load", "Transform" button at the bottom. I only have "OK". Why is that? I have pro license and I have most updated version of powerBI

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

4 жыл бұрын

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

@paspuggie48

4 жыл бұрын

Brill. Thank you 😎

@d.scovery8615

4 жыл бұрын

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

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?

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?

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!

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

3 жыл бұрын

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

@houstonfirstitdepartment9966

3 жыл бұрын

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

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

3 жыл бұрын

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.