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

Подписаться 91 тыс.

Просмотров 33 тыс.

50% 1

⏬ Click to check out the description for info and links⏬
Consolidate Multiple files From SharePoint Folder and how to debug and avoid errors
00:00 Intro
00:42 The Setup - Getting the correct folder path
01:54 Change SharePoint.Files to SharePoint.Contents
05:49 Adding new files (including problem files)
07:42 Identifying the problem files
11:15 Changing the Folder Path with the Split Out technique
Warning: to use SharePoint.Contents the user must hace access to to all levels of the SharePoint site.
The SharePoint.Contents method is the better approach for a quicker refresh, but you do need access to the Root SharePoint folder.
Sadly the button is not included in Excel 365 for Business . However you can still use the code
= SharePoint.Contents("YOURPATH/", [ApiVersion = 15])
Did you know I've written a book "Power BI for the Excel Analyst"?
pbi.guide/book/
Connect with me
wyn.bio.link/
accessanalytic.com.au/

Хобби

Опубликовано:

1 авг 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 132

@alexrosen8762 Год назад

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

@AccessAnalytic Год назад

Thanks for taking the time to let me know

@RamakaWarrior День назад

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

@AccessAnalytic День назад

😄Thanks for taking the time to leave a kind comment

@pan7373 2 месяца назад

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

@AccessAnalytic 2 месяца назад

You’re welcome

@shirleymoreman6725 Год назад

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

@AccessAnalytic Год назад

Cheers Shirley

@a66448579 10 месяцев назад

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

@AccessAnalytic 10 месяцев назад

Great!

@hulltl Год назад

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

@AccessAnalytic Год назад

Thanks, yep that’s a little hidden gem

@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

@davidfamilydoctor9430 6 месяцев назад

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

@AccessAnalytic 6 месяцев назад

Cheers, glad you’ve found it useful

@msantosh1220 Год назад

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

@AccessAnalytic Год назад

Glad you liked it

@tristangeoffroy9286 8 месяцев назад

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 8 месяцев назад

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

@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

@chrism9037 Год назад

Thanks Wyn very helpful!

@AccessAnalytic Год назад

Thanks for taking the time to let me know

@hannesbartsch5120 13 дней назад

You are the best!

@AccessAnalytic 13 дней назад

Thanks 😊

@NICO898ful Год назад

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

@AccessAnalytic Год назад

je vous en prie

@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

@tomstrack42 Год назад

Thanks for giving a Split! :)

@AccessAnalytic Год назад

😀

@anilb5836 5 месяцев назад

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

@AccessAnalytic 5 месяцев назад

Glad to help

@greciabateify Год назад

Thanks super useful

@AccessAnalytic Год назад

You're welcome!

@jitheshkarunakaran 2 месяца назад

Love it ... :)

@AccessAnalytic 2 месяца назад

Great to hear!

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

@boissierepascal5755 Год назад

💪🏼💪🏼💪🏼

@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

@saltyashiful 10 месяцев назад

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

@AccessAnalytic 10 месяцев назад

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

@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

@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 ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-09tvia_8ykI.html

@tannergervais6941 Год назад

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

@stharrell83 4 месяца назад

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 3 месяца назад

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 3 месяца назад

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

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

@benjaminlembke5934 9 месяцев назад

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 9 месяцев назад

Check out this ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-mgVnk4R79ac.htmlsi=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

@Donkeys_Dad_Adam Год назад

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

@Donkeys_Dad_Adam Год назад

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

@Donkeys_Dad_Adam Год назад

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

@AccessAnalytic Год назад

@@Donkeys_Dad_Adam Great to know!

@TheNtrsBOB 6 месяцев назад

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 6 месяцев назад

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

@AccessAnalytic 6 месяцев назад

I’m glad you figured it out

@sc71blazer 7 месяцев назад

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 7 месяцев назад

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.

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

@jorisvanh.9274 7 месяцев назад

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

@AccessAnalytic 7 месяцев назад

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

@reff3382 8 месяцев назад

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 8 месяцев назад

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

@davidfamilydoctor9430 6 месяцев назад

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

@AccessAnalytic 6 месяцев назад

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.

@JenMayB 4 месяца назад

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 4 месяца назад

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

@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

@user-ku2ic8md3n Год назад

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.

@user-ku2ic8md3n Год назад

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

@AccessAnalytic Год назад

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

@AlexisMaxwell-sk4bs 2 месяца назад

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 2 месяца назад

This video should help ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE--XE7HEZbQiY.htmlsi=LgUuDnwx64Ybq5C_

@AlexisMaxwell-sk4bs 2 месяца назад

@@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 2 месяца назад

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

@user-je8fy5gn9m 11 месяцев назад

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 11 месяцев назад

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

@AccessAnalytic 11 месяцев назад

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

@kushalthapa3548 4 месяца назад

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 4 месяца назад

Try this ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-BVhziaHBvS4.htmlsi=iOeiPdxqrW0aiIZY

6 месяцев назад

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 6 месяцев назад

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

@davidfamilydoctor9430 6 месяцев назад

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

@AccessAnalytic 6 месяцев назад

Does this help: Great tricks when loading Files From Folder if sheet names are different ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-BVhziaHBvS4.html

@cm00000 6 месяцев назад

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 6 месяцев назад

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 6 месяцев назад

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

@cm00000 6 месяцев назад

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

@AccessAnalytic 6 месяцев назад

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

@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 6 месяцев назад

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

@AccessAnalytic 6 месяцев назад

@cm00000 check out the custom function option mentioned at end of this video. See the link in its description to Imke’s post ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-mgVnk4R79ac.html

@rafiudeensarfaraz5898 11 месяцев назад

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 11 месяцев назад

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

@rafiudeensarfaraz5898 11 месяцев назад

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

@AccessAnalytic 11 месяцев назад

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

@rafiudeensarfaraz5898 11 месяцев назад

@@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 11 месяцев назад

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

@davidfamilydoctor9430 6 месяцев назад

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

@AccessAnalytic 6 месяцев назад

Yep