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

@AccessAnalytic

Жыл бұрын

Thanks for taking the time to let me know

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

@AccessAnalytic

Ай бұрын

You’re welcome

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

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

@AccessAnalytic

Жыл бұрын

Thanks, yep that’s a little hidden gem

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

@AccessAnalytic

Жыл бұрын

Cheers Shirley

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

@AccessAnalytic

9 ай бұрын

Great!

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

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

Thanks Wyn very helpful!

@AccessAnalytic

Жыл бұрын

Thanks for taking the time to let me know

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

7 ай бұрын

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

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

@AccessAnalytic

6 ай бұрын

Cheers, glad you’ve found it useful

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

@AccessAnalytic

Жыл бұрын

Glad you liked it

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

@AccessAnalytic

Жыл бұрын

je vous en prie

Thanks for giving a Split! :)

@AccessAnalytic

Жыл бұрын

😀

Love it ... :)

@AccessAnalytic

Ай бұрын

Great to hear!

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

Thanks super useful

@AccessAnalytic

Жыл бұрын

You're welcome!

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

@AccessAnalytic

4 ай бұрын

Glad to help

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é

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

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

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

@AccessAnalytic

9 ай бұрын

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

💪🏼💪🏼💪🏼

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

9 күн бұрын

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

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

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 kzread.info/dash/bejne/Ym2o2Muaj5vfm6w.html

@tannergervais6941

Жыл бұрын

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

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!

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

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!

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

6 ай бұрын

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.

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

@AccessAnalytic

6 ай бұрын

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

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

8 ай бұрын

Check out this kzread.info/dash/bejne/n5uK0M1tgpqfkcY.htmlsi=Jar4DAzJ819w2QHZ

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.

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

@AccessAnalytic

5 ай бұрын

Does this help: Great tricks when loading Files From Folder if sheet names are different kzread.info/dash/bejne/dIqc3MuaeKXcg5c.html

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

7 ай бұрын

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

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.

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

@AccessAnalytic

11 ай бұрын

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

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

3 ай бұрын

Try this kzread.info/dash/bejne/dIqc3MuaeKXcg5c.htmlsi=iOeiPdxqrW0aiIZY

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

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 kzread.info/dash/bejne/X4x5map-isW3mbw.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.

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

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

10 ай бұрын

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

@rafiudeensarfaraz5898

10 ай бұрын

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

@AccessAnalytic

10 ай бұрын

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

@rafiudeensarfaraz5898

10 ай бұрын

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

10 ай бұрын

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

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

5 ай бұрын

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

5 ай бұрын

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

@cm00000

5 ай бұрын

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

@AccessAnalytic

5 ай бұрын

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

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 kzread.info/dash/bejne/n5uK0M1tgpqfkcY.html

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

10 ай бұрын

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

@AccessAnalytic

10 ай бұрын

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

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

Is it possible to use another person's path?

@AccessAnalytic

8 ай бұрын

Yes if they’ve given you access to the path

Is this possible with power point?

@AccessAnalytic

Жыл бұрын

Do you mean Power BI? If so then yes

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

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

@AccessAnalytic

3 ай бұрын

Check out the description section for instructions when option is missing

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

@AccessAnalytic

5 ай бұрын

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

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

@AccessAnalytic

6 ай бұрын

Yep