How to combine (and debug) Excel files From SharePoint Folder

[@alexrosen8762]

Extremely useful tutorial on a high level. Big thank you 🙏

[@AccessAnalytic]

Thanks for taking the time to let me know

[@brandonbuck8882]

I have struggled with this so often. This video is the only thing that has ever made sense and helped through! Thank you!!!

[@AccessAnalytic]

Thanks for taking the time to leave a kind comment

[@hulltl]

Loved the split feature. Very useful. Makes it easier to drop in parameters etc.

[@AccessAnalytic]

Thanks, yep that’s a little hidden gem

[@pan7373]

Helped me combine more than 70 separate excel sheets in SharePoint into one! Thanks for the help!

[@AccessAnalytic]

You’re welcome

[@shirleymoreman6725]

Totally agree on error messages! Great tip for the "Split" - I hadn't seen that one before.

[@AccessAnalytic]

Cheers Shirley

[@mjb4365]

The Bonus tip should be renamed to essential tip. Genius. I learn so much from you, Wyn. Thank you.

[@AccessAnalytic]

Thank you, and thanks for taking the time to leave a kind comment

[@Bhavik_Khatri]

Extremely useful with error checks, the duplicate feature is a great addition. I have created a budget consolidation tool that uses SharePoint folder path and folder name as two variables, allowing the user to be self-sufficient. Furthermore, I implemented your trick of capturing column headers and then expanding the table to avoid hard-coding column names. The tool is working well, and I would like to thank you for helping me out with your videos.

[@AccessAnalytic]

Thank you for taking the time to let me know you found it useful

[@sarasartori9216]

Thank you for sharing! I was working on the consolidation of files from SharePoint, before finding and watching this video. These are great recommendations and they will save me lots of heartaches...will go and implement them in my query right away!!! Thank you again!

[@AccessAnalytic]

Excellent! Thanks for taking the time to leave a kind comment

[@tristangeoffroy9286]

Once again I’m right into this kind of trouble, and it’s very helpful seeing you going through it. Thank for the « split » and also the dedicated error problems. ;-) and for the English lesson by the way, you, pronouncing correctly the errors messages:-)

[@AccessAnalytic]

Glad to help ( in a variety of ways 😀 ) I appreciate you taking the time to let me know you found it useful

[@ruchismitasahoo2955]

Thank you so much😊😊 It is indeed very detailed with step-by-step explanation. 😊

[@a66448579]

Have been trying to do this on and off for months. This finally made it clear! thanks

[@AccessAnalytic]

Great!

[@khoa21]

Exactly what I was looking since a while! Thank you so much!

[@AccessAnalytic]

You’re very welcome

[@RamakaWarrior]

🤓 You're my Power Query hero. Every video is an enrichment.

[@AccessAnalytic]

😄Thanks for taking the time to leave a kind comment

[@ShelliG]

I can't load my SharePoint folder in Excel but can in PowerBI and this helped me get started - THANKS!

[@AccessAnalytic]

That’s odd, they should work the same. Glad this helped.

[@vladog1834]

Wow - you are great teacher

[@AccessAnalytic]

Thanks for taking the time to leave a kind comment

[@msantosh1220]

Yes, this was helpful. Thanks a lot for the tip of using Split after source Step name.

[@AccessAnalytic]

Glad you liked it

[@davidfamilydoctor9430]

Love this approach because the main query will still run, though with out some data.

[@AccessAnalytic]

Cheers, glad you’ve found it useful

[@persada7761]

Thankyou for sharing, what an amazing tipss

[@AccessAnalytic]

You’re very welcome 😀

[@xRAFAx86x]

Excelent! Very helpful Guide!

[@AccessAnalytic]

Cheers 😀

[@chrism9037]

Thanks Wyn very helpful!

[@AccessAnalytic]

Thanks for taking the time to let me know

[@tomstrack42]

Thanks for giving a Split! :)

[@AccessAnalytic]

😀

[@mirrrvelll5164]

Indeed they need be specific with errors =))) that trick with Split is amazing. Error table is a good thing to monitor.

[@AccessAnalytic]

I appreciate you taking the time to let me know you found it useful

[@oliverantoniou]

Great tips as always Wyn, particularly liked the Split hack. The business I work for has recently moved everything to SharePoint so I'm spamming your channel!

[@AccessAnalytic]

Good stuff! 😀

[@AccessAnalytic]

Hopefully you found this playlist kzbin.info/aero/PLlHDyf8d156W_I_ycA7kbfLKAej54p9Un&si=SSlvQBPcUSQypfyY

[@oliverantoniou]

Legend

[@AccessAnalytic]

@oliverantoniou cheers

[@anilb5836]

Very Good sir 👏, nice trick I was facing this issue in my daily work....

[@AccessAnalytic]

Glad to help

[@NICO898ful]

Merci Win. A great video. I put it in my favorit folder 🙂

[@AccessAnalytic]

je vous en prie

[@hannesbartsch5120]

You are the best!

[@AccessAnalytic]

Thanks 😊

[@greciabateify]

Thanks super useful

[@AccessAnalytic]

You're welcome!

[@IvanCortinas_ES]

The Power Query interface error messages are disastrous for a lay user. One has to be strong and undertake detective actions. Great tutorial Wyn.

[@AccessAnalytic]

Thanks, yep it's poor and hopefully will be addressed one day

[@JaniceCook-jx8pw]

Great and very useful. Would it be possible to define the source location in a cell in the worksheet (maybe a 'Named Range' for ease of reference) and then refer to this in the PQ. This would then allow easy maintenance without having to edit the PQ - especially as the source may be referenced differently for different users.

[@AccessAnalytic]

Yep, you can do, it's a bit tricky with SharePoint.Contents as you have to jump through the different folders. General Concept (not the exact solution) is Create the named cell with the filepath typed into it, right click on it, use Get Data from Table/Range. Once that loads Right Click on the filepath text and "Drill Down" then you can reference that Query Name in your Source step in the SharePoint folder

[@pavelbursa9247]

@@AccessAnalyticthis is my problem to - i want to share with colleagues power query xlsx with data source from onedrive / sharepoint data.

[@AccessAnalytic]

@pavelbursa9247 see if this video gives you some ideas ( the macro bit is not so relevant as it’s not straightforward to get the OneDrive / SharePoint path ). Maybe you could provide a drop down list of Partha instead. Power Query for Excel with a File Selector Macro kzbin.info/www/bejne/g4vTqIWaZ9h-gKc

[@josecarlosconejo5724]

I just did exactly that yesterday at work, retrieving budget data templates from departments from a SharePoint folder. Don’t know whether to be happy or scared with this video or your mind-reading abilities 😂😂😂

[@AccessAnalytic]

Good work José

[@tannergervais6941]

Very helpful tutorial! What if you are consolidating multiple files like this based on a table inside the files. Like this example is using the sheets called “Data”. Now say the table template/ format has changed / been updated. A new column has been added to the table. How do you get that new column to appear in your total consolidated table ? Is that even possible? When I go to “select columns” the new column is not available. My thought is it would be available and once selected the value of that column in earlier tables would be null. Love your videos. Hopefully my question makes sense!

[@AccessAnalytic]

Hi, maybe this video will help. It’s the expand step that needs fixing kzbin.info/www/bejne/ZmrXp5yXlJ2soas

[@tannergervais6941]

Yes! This is exactly what I have been looking for! Thank you!

[@HarishKpy]

Excellent Tutorial. Could you please clarify my question? I have the list of excel files with a dynamic sheet name like date with sheet name . How can I combine this sheet data in power query ?

[@AccessAnalytic]

Thanks, I’ll do a video on that soon as it’s a good question essentially don’t use the combine option, instead Transform and add a Custom Column =Excel.Workbook([Contents]) There’s more steps but that’s the starting point

[@dbalkin777]

What does split in the advanced editor do? That was the only part of the video I was unclear on.

[@AccessAnalytic]

Ah, technically it shouldn’t do anything as it’s just a new step name that simply refers to te the previous step. However, adding an intermediate step somehow fleeces the single “navigation” step to be split out into its component parts. You could use any word for that new step name. Doesn’t have to be split.

[@stharrell83]

Great tutorial, thank you! I seem to have a slightly different problem- when there is an error in the excel sheets I'm consolidating (e.g. the cell value is '#REF!') power query seems unable to recognize this an error and classify it as such using this process, but reports DataFormat.Error: Invalid cell value. How can I learn which file is causing the query to halt?

[@AccessAnalytic]

I remember this happening to me in the past. I’d have thought the technique in the video would work? What’s the exact scenario, and when’s the warning popping up ( is it when you try to pick from a filter? ) I think what I had to do is go into the sample file and change the Source{0} to Source{2} then 3 4 5 etc and check the Transform sample file each time.

[@stharrell83]

@@AccessAnalytic It was not clear at which step the warning was popping up: it would appear without much information at all. I think it was a problem with one of the filters, and so I duplicated the query, removed all of the filters, kept errors, eliminated all columns but the filename which enabled me to go in and find the sheets with the problems. After fixing them, problem solved!

[@jitheshkarunakaran]

Love it ... :)

[@AccessAnalytic]

Great to hear!

[@Adam_K_W]

Hi Wyn, Remember back a couple of months ago on an earlier video where you explained switching from SharePoint.Files to SharePoint.Contents, there was a bug that was causing a failure? I never heard back on that and was wondering if you figured out what that was about? We had a bit of a back and forth on it and I could get it to work if I rolled back an API from v15 to v14, but then it was very slow.

[@AccessAnalytic]

Hi Adam, they fixed the bug. Should work fine now

[@Adam_K_W]

@@AccessAnalytic AWESOME! I will check it out and let you know if I have any issues with it! Thanks for letting me know!

[@Adam_K_W]

@@AccessAnalytic Tested this morning in the Query Editor in BI, worked like a Charm!

[@AccessAnalytic]

@@Adam_K_W Great to know!

[@sc71blazer]

Probably a stupid question, but why did you unpivot the columns in the Transform Sample File query instead of the Consolidation query? Is it just a preference, or is it better to do it to the sample query? If it is better, why types of transformation steps are better to do in a sample Transform query?

[@AccessAnalytic]

Sometimes you have to unpivot the individual files first as their structures may be slightly different. I'm not 100% sure which method is quicker, I tend to do the file level re-organising at the individual file level to keep the steps grouped together. There's no clear right or wrong way here in my experience.

[@jorisvanh.9274]

Not all Excel versions have this Sharepoint connector. Excel 365 Business Premium doesn't seem to have it for example ..

[@AccessAnalytic]

Sadly the button is not included in Excel 365 for Business . However you can still use the code in a blank query = SharePoint.Contents("YOURPATH/", [ApiVersion = 15])

[@kushalthapa3548]

What if every excel file has different sheet names. Such file first excel file will have sheet1, 2nd excel file will have sheet2.... how to handle the error.

[@AccessAnalytic]

Try this kzbin.info/www/bejne/eIfLq5yXfaepiZYsi=iOeiPdxqrW0aiIZY

[@saltyashiful]

Thank you for sharing great tip. Is there a way to get files in a restricted access folder using this method?

[@AccessAnalytic]

You’re welcome, I’m not sure what you mean by restricted access

[@benjaminlembke5934]

Hi, there is a problem I have with using Contents instead of Files. If you are working with files in folders and subfolders on different layers I havn't found a solution for that. Seems like an easy thing but I can't figure it out. Any ideas?

[@AccessAnalytic]

Check out this kzbin.info/www/bejne/o5i5n55qh5xsl8Usi=Jar4DAzJ819w2QHZ

[@ennykraft]

Great video. Thank you very much! A couple of things I'd like to mention: I have a 365 Business account and the From Sharepoint option is missing. I looked it up and apparently you need an Enterprise account for that. Is that new? I have to create a blank query and then type = SharePoint.Files(" URL of my Sharepoint site ", [ApiVersion = 15]) That works just as well. Regarding .Files compared to .Contens: I prefer .Files since I put my files in uniquely named folders and then do a contains search in the paths. I also detest helper queries so instead of the two arrows in content, I add a user defined column with Excel.Workbook([Content]) since that way I am not depended on the properties of the first file. I can set my own filters for what I want to extract. Empty files or those with different names won't cause any errors if the filters are set to the correct properties.

[@AccessAnalytic]

Thanks Point 1: not new, I added a note in the description Point 2. The .Contents method refreshes a lot quicker ( depending on amount of files in your SharePoint site ) Point 3. Fair enough if you don’t like helpers. I often find the transform sample file super helpful

[@davidfamilydoctor9430]

Would be helpful if the sample file step could accommodate worksheets/tabs with any file name.

[@AccessAnalytic]

Does this help: Great tricks when loading Files From Folder if sheet names are different kzbin.info/www/bejne/eIfLq5yXfaepiZY

[@davidfamilydoctor9430]

Why choose 'load to connection only' but then immediately right-click the query and 'load to' a table? How does this help?

[@AccessAnalytic]

Otherwise all the helper queries load which you then would have to individually disable from loading’s so it’s simply less clicks doing it this way.

[@TheNtrsBOB]

Great video, thanks for sharing! After I combine excel files with the exact same column headers (I just created a test using 2 files that only have 3 columns: col1, col2, col3), the headers are showing up as a row in each file and the Power Query table just has the default Column1, Column2, etc. I didn't see you perform any steps involving promoting or removing headers, so I'm at a loss for what I'm missing.

[@TheNtrsBOB]

I figured it out. The data in every Excel file has to be a table with the same name.

[@AccessAnalytic]

I’m glad you figured it out

[@AlexisMaxwell-sk4bs]

I'm trying to follow along, but the files that I'm working with are in a Shared Folder on SharePoint; not in My Files so I'm really struggling with how to get to the Shared Folder to access the sheets that I'm trying to combine. This is for a team project so multiple people need to be able to get into the folder. Does that make sense? I copied the path like you tell us in the beginning of the video in order to paste it into Get Data > From File > From SharePoint Folder but I'm not able to find the Shared Folder to go any further than that. :( Any suggestions?

[@AccessAnalytic]

This video should help kzbin.info/www/bejne/Y4moaHt7j8eEn7ssi=LgUuDnwx64Ybq5C_

[@AlexisMaxwell-sk4bs]

@@AccessAnalytic Darn! I tried following along with the video via the link you included in your reply and still no luck. I am able to access my personal files, but I cannot figure out how to find the files that are saved in the Shared folder that my team uses. I'm at a loss of what to do...Thanks for trying.

[@AccessAnalytic]

@AlexisMaxwell-sk4bs - did you change SharePoint.Files to SharePoint.Contents ? There should then be a “Table” next to Shared Documents or something named similar to that, it’s sometimes called something a bit different.

[@DonTee-k5s]

My company uses 365 and I can't find Get Data from SharePoint Folder. Is it because of the type of license? Can I use from Web instead?

[@AccessAnalytic]

Possibly due to have 365 Business rather than one of the slightly more expensive licences. You can actually type the code in as per the start of the process i.e. SharePoint.Contents( ... ) it will work, there's just no button to help you. If you have Power BI Desktop installed, you could use that and then copy the Power Query into Excel.

[@DonTee-k5s]

@@AccessAnalytic Thanks, do you have a video on how to do this?

[@AccessAnalytic]

@@DonTee-k5s Create a blank Power Query then type something like this into the formula bar = SharePoint.Contents("YourRootFolder", [ApiVersion = 15])

[@Mrk.14]

Hi thanks for this video but I don't have option "from SharePoint Folder", why?

[@AccessAnalytic]

Hi, some versions of O365 like Home / Student / Business don't have the button, which is a very odd decision by Microsoft. If you copy the query from Power BI or use this code in the source step of a blank query it should work anyway = SharePoint.Contents("YOURPATH/", [ApiVersion = 15])

[@asrsinha_10]

Thanks, It's very great and useful video. However, I'm getting and error as [Expression. Error] The key didn't match any rows in the table in excel while combining files. How to get it resolved.

[@AccessAnalytic]

Make sure the sheet in each file is named EXACTLY the same. And also see if any of my debugging steps can assist in identifying the problem

[@asrsinha_10]

@@AccessAnalytic Yes it worked and fixed the error. Thank you!

[@AccessAnalytic]

Great, thanks for letting me know

[@boissierepascal5755]

💪🏼💪🏼💪🏼

[@rafals2041]

I have 6 main folders (BU regions) in sharepoint and 12 subfolders for each month of the year separately (January to December). Once a month I need to create a report and consolidate data from all files according to the month.. How to consolidate data from sharepoint subfolders using powerquery? Do I have to change the path each time if I need to refresh data for the next month? any solution with dynamic connection or parameters ?

[@AccessAnalytic]

Maybe this will help kzbin.info/www/bejne/o5i5n55qh5xsl8Usi=LhrcifYzLTkQfDCu

[@qrsimon]

Omword nice

[@reff3382]

Hi Wyn, I get an error that says “We didnt recognize the format of your first file….”. All my files are .xlsx . Is there a work-around for this?

[@AccessAnalytic]

Are you able to use the normal get Data from Excel if you save that first file to your desktop. Just to text if that works ok

[@SamairaAnkit]

hi I have followed all of these steps and it works but unfortunately when I try to update the other excel file which are the once that are connected there seems to be a problem when refreshing. it keeps showing as there is an expression error. there is a key that doesnt match any rows in the table. how to resolve this?

[@AccessAnalytic]

Maybe a sheet with a different name, or #N/A n one of the files. Difficult to tell sorry.

[@AccessAnalytic]

Start with 2 files in the folder, if it works add another dnd another until you hit the issue if the debugging tip isn’t working

[@efrat2211]

Is it possible to use another person's path?

[@AccessAnalytic]

Yes if they’ve given you access to the path

[@JenMayB]

For the first time in my life I’m getting an error and binary combine tables that i cannot figure out. It is not invoking the transform tables with the helper queries. It’s saying it does not recognize the file type (it is the sharepoint address for my business). Ive cut and pasted the step for hidden files from another query for another folder. No luck.

[@AccessAnalytic]

Odd , maybe post to techcommunity.microsoft.com/t5/microsoft-excel/ct-p/Excel_Cat?WT.mc_id=M365-MVP-5002589

[@rafiudeensarfaraz5898]

I have a query. if there are any changes that were to be made in any of the excel files (not the combined one) will it also get updated in the combined excel or is there any thing else we have to do?

[@AccessAnalytic]

If it’s just extra rows of data being added to the source files then on refresh those changes will be brought in

[@rafiudeensarfaraz5898]

what about the rows that are already in there but there are changes in the specific rows but different columns@@AccessAnalytic

[@AccessAnalytic]

@rafiudeensarfaraz5898 every time you refresh its like starting from scratch. It reimports it all

[@rafiudeensarfaraz5898]

@@AccessAnalytic hi i have tried out the way that is mentioned but I am facing a new problem which is there is null values that is being shown. But when I checked all of the column names are the same so why is it still showing null values?

[@AccessAnalytic]

One column will be spelled slightly differently ( a space or capital letter is often the cause )

[@ChuanHua]

what if I have multiple sub folders in that SharePoint folder, is there a workaround for it?

[@AccessAnalytic]

Potentially able to use the transform option to then expand out sub folders contents too. ( rather than using the Combine and Transform shortcut )

[@cm00000]

@@AccessAnalyticdo you have a video that will show this?

[@AccessAnalytic]

@cm00000 check out the custom function option mentioned at end of this video. See the link in its description to Imke’s post kzbin.info/www/bejne/o5i5n55qh5xsl8U

[@NancyMancarious]

Unfortunately, I don't have the "SharePoint Folder" option.

[@AccessAnalytic]

Check out the description section for instructions when option is missing

[@cm00000]

Im having issues in power query where if some columns i need to combine from multiple files in sharepoint are formula-based not values (like xlookups), Power query sometimes import those columns and return entries as null if i dont open the excel files first to recalculate, save, and close them. Is there a workaround so i wont have to open reclaulate and resave excel source files as they are a lot and defeats the purpose of automation i want to achieve…

[@AccessAnalytic]

Power Query will just read in the results of formulas. Not sure why your XLOOKUPs aren’t populating in your closed workbooks sorry. Referencing external SharePoint files maybe? Or are dynamic arrays?

[@cm00000]

@@AccessAnalytic i have columns whose results are from combined filter and vstack. Does power query has limitations with dynamic arrays?

[@cm00000]

Im so sorry im really new to this and i found your channel to be one of the best out there

[@AccessAnalytic]

Should be ok. I heard of an issue when referencing external SharePoint files in arrays at some point ( I think )

When clicking the double errors to combine the files it gives error “we didn’t recognized the format of your first file. Please filter the list of files so it contains only supported types”. All my files are excel workbooks with the same layout in each file

[@AccessAnalytic]

I’d start with 1 file in the folder and if that works keep adding files until you hit the problem. If the files are xls formats or exports from a system they may not be valid structures for this approach

[@scapaflw]

Is this possible with power point?

[@AccessAnalytic]

Do you mean Power BI? If so then yes

[@vincenzonosso7400]

I can't find any "From SharePoint folder" in Excel... :(

[@AccessAnalytic]

Sadly the button is not included in Excel 365 for Business . However you can still use the code = SharePoint.Contents("YOURPATH/", [ApiVersion = 15])

[@davidfamilydoctor9430]

"This does rely on every file having a sheet called 'data'." Noted.

[@AccessAnalytic]

Yep