Combining Multiple Files from a folder using Power Query in Excel or Power BI ( ⚠️see description )

Рет қаралды 80,120

Күн бұрын

⚠️ see below for link to updated video on SharePoint connection ⚠️
How to use Power Query for Excel and Power BI to consolidate multiple files into a single table of data, whether you're using OneDrive , SharePoint or a traditional network folder.
As well as showing the basic steps, this video explains the inner workings of the Power Query helper functions so you fully understand how it works
00:00 Intro
00:19 Connecting to a folder on your C :Drive / Network
02:39 Using the Transform Sample File to clean up your data
06:09 The Helper Queries explained
10:08 Connecting to a folder on SharePoint or OneDrive for Business
*******************************************************************************
⚠️Check out my updated video (March 2022) on using SharePoint.Contents ⚠️
• The best way to connec...
*******************************************************************************
How to connect to a file on SharePoint and OneDrive
• How to use Power Query...
My Articles on LinkedIn
/ wynhopkins
Our Website and Training Services
accessanalytic.com.au/

Пікірлер: 252

@alexkim7270 3 жыл бұрын

Wow this is great! I've been curious to know how the other buttons work in that interface but I simply cannot afford the time to bump into obstacles and spending the rest of the day working on fixing those obstacles. I didn't even know such trick existed 3 years ago! Thanks a lot for sharing your knowledge, Wyn! This is so good!

@AccessAnalytic 3 жыл бұрын

Fantastic Alex, glad to help, thanks for leaving a comment and letting me know you liked it.

@allardvanpelt6767 5 ай бұрын

Hi, no starting at 4:00

@allardvanpelt6767 5 ай бұрын

sorry comment :-)

@bobwalden7059 2 жыл бұрын

Best explanation of files in folders I’ve found on YT. THANK YOU!

@AccessAnalytic 2 жыл бұрын

Thank you very much Bob. I appreciate you taking the time to leave a comment.

@joedi 2 жыл бұрын

I can’t believe he doesn’t have more subs. He was the only one who answered my struggle with SharePoint. Not this video but the SharePoint folder one. Mind blowing!

@AccessAnalytic 2 жыл бұрын

Thanks dee. We're on an accelerating trajectory! Share the word. 😁

@GrainneDuggan_Excel 3 жыл бұрын

Thanks Wyn. Clear description of the intermediate steps. I like your tip to have a reference query for the folder. That saves so much effort when the path changes - which happens all too often.

@AccessAnalytic 3 жыл бұрын

Thank you Grainne D

@nataliaoleksander6386 2 жыл бұрын

I like this guy. easy to understand. No nonsense. Very explanatory.

@AccessAnalytic 2 жыл бұрын

Thanks, this guy is grateful for the kind comment 😀

@victorhansson3410 2 жыл бұрын

Oh wow, this was great. I've watched a few videos on this but none of them explained it as well as you did. Thanks a lot for explaining the "why" and not just the "how"!

@AccessAnalytic 2 жыл бұрын

You’re welcome Victor, thanks for your comment

@700997372mp 3 жыл бұрын

Great video, I've done this many times and never really thought about changing the Transform Sample File. Good explanation of what the steps are doing.

@AccessAnalytic 3 жыл бұрын

Thanks for the feedback Marcus

@federicotait6470 4 ай бұрын

Helped me a lot to understand the principles of Power Query. Very well explained and easy to follow. Brilliant! Thank you so much!

@AccessAnalytic 4 ай бұрын

You’re welcome. Thanks for taking the time to leave a kind comment

@jarich7667 Жыл бұрын

This was such a big help!! I stepped into a project and things are very different from how I usually work. This explanation helped me immensely!! I appreciate your help more than I can say! Thank you!!

@AccessAnalytic Жыл бұрын

You're welcome Jason. Thanks for taking the time to let me know it helped. Much appreciated.

@YasirKhalid1 3 жыл бұрын

Great vid Wyn! Thanks for explaining step-by-step n the differences between the two approaches

@AccessAnalytic 3 жыл бұрын

No worries Yasir

@joymedvecky2659 Жыл бұрын

What a GREAT explanation! Very clear, conscience and in plain terms - I can now properly move forward with my Power BI Power Query, thanks 🙂

@AccessAnalytic Жыл бұрын

You’re welcome. Thanks for the kind feedback

@sedarathnadcd Жыл бұрын

Woooow. It is comprehensive explanation. i was trying two days in youtube searching an explanation like this. Thank you Access Analytic. Specially meaning of "Content (0) ", details abt transform folder.

@AccessAnalytic Жыл бұрын

Thanks for taking the time to leave a kind comment Priya

@venkatreddyb628 Жыл бұрын

It is excellent. I clearly understand why so many queries were created while importing from a folder. Now, I can re-use the helper queries for multiple instances of imports from the folders. Thank you for your effort...

@AccessAnalytic Жыл бұрын

Excellent, glad to help Venkat

@jesamujong7477 6 ай бұрын

My go-to guy! Precisely what I needed. Thank you, Wyn!

@AccessAnalytic 6 ай бұрын

Cheers, make sure you check out the updated version of this video too. Link is in the description

@oladman9058 2 жыл бұрын

You will never know how much you helped me with this video. Many thanks and God bless you real good.

@AccessAnalytic 2 жыл бұрын

Thank you very much, glad it helped

@MrKamranhaider0 2 жыл бұрын

Oh you help me a lot .Before this I had to build customs function for transformation.

@AccessAnalytic 2 жыл бұрын

Glad tp help Kamran

@heshamfouad8886 3 жыл бұрын

Specific, to the point, informative, simply brilliant Thanks so much for sharing your knowledge, and much more thanks for sharing them in a smart time saving videos :)

@AccessAnalytic 3 жыл бұрын

Thank you so much Hesham 😀

@timdowden7725 2 жыл бұрын

Thank you for this very helpful tutorial. I'm new to PQ, and was able to start using it effectively after this 20 minute tutorial NICE!!

@AccessAnalytic 2 жыл бұрын

That’s great Tim, thanks for letting me know

@user-no5el6dv4p Жыл бұрын

I spent several hours trying to figure out on my own how I’d broken my attempt at this today 😂 thanks for the help! Solved my problem

@AccessAnalytic Жыл бұрын

Glad it helped!

@PonySldr1 3 жыл бұрын

Excellent vid. Goes to prove you can always learn something even if you've been doing a process for a long time. Good on ya Wyn

@AccessAnalytic 3 жыл бұрын

Glad it was helpful. Thanks for letting me know.

@bjorns1039 2 жыл бұрын

You can do it in many ways, but this was really lean och quick! The best way! Many thanks!!

@AccessAnalytic 2 жыл бұрын

You’re welcome

@mariaen8394 2 жыл бұрын

Thanks so much for explaining this transformation. You really simplified it for me. :)

@AccessAnalytic 2 жыл бұрын

Thank you for taking the time to leave a comment Maria

@barttitulaerexcelbart9400 3 жыл бұрын

Very nice video! thank for explaining all those intermediate steps!

@AccessAnalytic 3 жыл бұрын

You’re welcome Bart

@YouExcelTutorials 3 жыл бұрын

Thanks for sharing Wyn! Very clear and detailed 👍

@AccessAnalytic 3 жыл бұрын

Cheers, I appreciate you leaving the feedback

@BoredAtWork2000 2 жыл бұрын

Exactly what I was looking for, thanks!

@AccessAnalytic 2 жыл бұрын

Great, thanks for letting me know

@IvanCortinas_ES Жыл бұрын

Brilliant explanation. Thank you Wyn!!!

@AccessAnalytic Жыл бұрын

Cheers Iván

@user-gd2bd7gh5o Жыл бұрын

Tks so much for the detailed video! I just combined 10 files with Power Query !

@AccessAnalytic Жыл бұрын

Awesome!

@rogergrinde7321 Жыл бұрын

This is fantastic! Thank you. This also helped me learn about parameters

@AccessAnalytic Жыл бұрын

Great! Thanks for letting me know

@bradfry6396 2 жыл бұрын

Thanks Wyn, you have saved me again 👍

@AccessAnalytic 2 жыл бұрын

No worries Brad, thanks for letting me know you're finding these useful

@Ganja1974 2 жыл бұрын

Excellent! used it before, but never realized how it can be improve the overall process

@AccessAnalytic 2 жыл бұрын

Glad it was useful Vasif

@timpullen6487 7 ай бұрын

What a brilliant lesson - Thank you very much. (I'm from a sound engineering background - When listening on headphones there is a lot of low frequency interference - bangs bumps etc. - I would suggest that by putting a low frequency filter on the audio, removing everything below 100 Hz.

@AccessAnalytic 7 ай бұрын

Thanks, hopefully the sound has improved in my more recent videos. This was recorded few years ago

@rebecavarela5804 Жыл бұрын

Thanks for the perfect and paused explanation.

@AccessAnalytic Жыл бұрын

You're very welcome

@leebecker8255 2 жыл бұрын

Really helpful video, thanks Wyn!! I particularly found the explanation of the helper files useful, and I didn't know you can use the Transform Sample file to tidy up the files before they get combined. I'd previously been doing some awkward thing to filter out all the column headings after they had been appended...🙄 BTW, I'm really looking forward to series 2 of your podcast too :D

@AccessAnalytic 2 жыл бұрын

Great Lee! Episode 1 is out now 😀

@leebecker8255 2 жыл бұрын

@@AccessAnalytic oooh thanks! I didn't even check 🤦

@perthling Жыл бұрын

Wow! Your walkthrough of the helper queries was more profound that I thought it would be. Opened my eyes to even more Power Query potential. I've been fooling around with PQ for a while, but now you make me want to go steady. 🤣

@AccessAnalytic Жыл бұрын

😳😆

@perthling Жыл бұрын

I'm signing up for the training tonight. A couple of years ago I co-lead an initiative to completely redesign reporting for our organisation's project management office and we developed portfolio wide dashboards. It worked great, but I would be hard-pressed to do it from the ground up without researching afresh all the various techniques. I can already see heaps in your approach that I would have benefited from. Really wish I knew about this back then! (PS I'm Perth-based too).

@AccessAnalytic Жыл бұрын

That’s great Luke!

@AccessAnalytic Жыл бұрын

@@perthling which training were you signing up for?

@nickvanmaele8059 Жыл бұрын

I finally understood what was going on in there. Thanks Wyn.

@AccessAnalytic Жыл бұрын

Thanks for taking the time to leave a kind comment Nick

@HassanRaza-pe5rh 2 жыл бұрын

Very clearly explained. Thank you sir!

@AccessAnalytic 2 жыл бұрын

You're welcome Hassan, thanks for the kind comment

@roberthblanchard Жыл бұрын

Thank you! You really saved me at work today!

@AccessAnalytic Жыл бұрын

You're welcome Robert, thanks for taking the time to let us know we've helped

@mnowako 2 жыл бұрын

Another great video. Thank you!

@AccessAnalytic 2 жыл бұрын

Cheers Mariusz

@the_imonem 2 жыл бұрын

Thank you, pretty informative!

@AccessAnalytic 2 жыл бұрын

Cheers Amr

@MrWish332 2 жыл бұрын

Thank You So Much, This is really very useful.

@AccessAnalytic 2 жыл бұрын

You’re welcome Vishal, thanks for taking the time to leave a comment.

@jacoblee1954 2 жыл бұрын

Thanks for doing this video it is really helpful .

@AccessAnalytic 2 жыл бұрын

No worries Jacob - thanks for letting me know

@danmilka 10 ай бұрын

Awesome, thank you very much, you saved me!

@AccessAnalytic 10 ай бұрын

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

@MayankRaoka 3 жыл бұрын

Very useful and simple explanation

@AccessAnalytic 3 жыл бұрын

Thank you Mayank

@williamarthur4801 Жыл бұрын

That was really good, I've always used add column excel workbook, but even with a an excel file I just expanded in the binary and was able to make changes using the generated function,

@AccessAnalytic Жыл бұрын

Thanks

@zahrakhan5329 2 жыл бұрын

Thank you, this is so useful! Just wondering if it's possible at all to use this process to combine multiple excel Sharepoint files with some files that have a few additional columns. The main columns that I need to combine are all named the same in each file, but some have extra calculation columns that are unnecessary for my consolidation. Any insight on how to transform this would be really appreciated!

@AccessAnalytic 2 жыл бұрын

Yes, same process should work. Just remove the columns you don’t need as one of the final steps in your consolidated table If your Transform Sample file step refers to specific columns then ensure the code is only referencing the columns that exist in all files.

@dennisjohnson9753 8 ай бұрын

This is a great video in the series on this process. Question - in the file name there is data that is needed in the record - how would I pull the file name into a new column before the combine where I lose that?

@AccessAnalytic 8 ай бұрын

Thanks Dennis, in the Consolidation query created at around 14:20 there should be a Removed Other Columns step with a cog next to it. Maybe one of the columns being removed there for you is Source Name ?

@Luciano_mp 3 жыл бұрын

Great. Thank you!

@AccessAnalytic 3 жыл бұрын

You’re welcome Luciano

@antronchestertonian9465 Жыл бұрын

Super helpful. I was wondering what those autogenerated steps were doing and how they were created in an existing query I was looking at.

@AccessAnalytic Жыл бұрын

Glad it was helpful!

@othukeajaye2415 Жыл бұрын

Wow, this was really great and easy to understand. You mentioned that it would be a lot harder if the source files were different but not impossible. Do you have a video on that or can you point me to a resource on what to do in that scenario? Thank you

@AccessAnalytic Жыл бұрын

The main approach is to put the different files in a different folder, repeat the exercise and then append the final outputs into one table before loading. I don’t have a video on that.

@suheilsamara7955 2 жыл бұрын

Thanks Wyn, Was wondering which is more efficient, to apply the transformation on the sample file or on the consolidated file. I have noticed no major performance difference in both scenarios while combining 50+ files in Power BI query. What do you suggest. Thank you.

@AccessAnalytic 2 жыл бұрын

Hi Suheil, I’m not aware of any performance differences. From a maintenance point of view I do as few steps as necessary in the transform sample file, but that’s just a personal preference.

@terrifayle6685 3 жыл бұрын

Very freakin' useful, thank you!! I tried load from folder multiple times and not understand all the queries created was an issue, so I'd delete and load individually. I'm going to give it a whirl!

@AccessAnalytic 3 жыл бұрын

That’s great Terri 😁. Thanks for letting me know

@yadukrishnankv5265 11 күн бұрын

Wow, great video! Thank You. I have a case where I want to connect to a hidden tab in an excel file stored in network, is it possible to do that? When I click combine on my file, it does not show up the hidden tabs.

@AccessAnalytic 11 күн бұрын

You could pick a visible sheet, then in the Transform Sample File query click on Navigation and change the sheet name in the formula bar. You'll likely need to deleted the change type step at the end of your consolidation query

@bryant9350 Жыл бұрын

Wonderful explanation, thank you! May I ask how to remove the last column of all the files in the folder? It is worth noting that last column, in my case, is not always the same column in every file (ex. some time it's column R, other times S and so on). Thanks in advance

@AccessAnalytic Жыл бұрын

Maybe go to the transform sample file and add this after the Promoted Headers step = Table.SelectColumns(#"Promoted Headers", let ListOfHeadings = Table.ColumnNames( #"Promoted Headers" ), NumberOfHeadings = List.Count( ListOfHeadings ), HeadingsToKeep = List.FirstN(ListOfHeadings,NumberOfHeadings-1 ) in HeadingsToKeep )

@annisaaprima9688 Жыл бұрын

Hi Thank you for the excellent sharing! A bit question, how would you the power query if the Share Point Folder are owned by other people (only shared to us)? Many thanks in advance

@AccessAnalytic Жыл бұрын

You’re welcome. It should work fine.

@g.mayerlingdelgado6346 Жыл бұрын

Great info, do you have a recommendation when within a Share Point Location we want to append certain (not all) the files in that folder?

@AccessAnalytic Жыл бұрын

You can apply a filter in one of the first few steps.

@Joann7000 2 жыл бұрын

thanks for this video! my issue is clicking on refresh all takes time to update content by triggering the external connections. So, i need to automate this. is it possible to refresh via power automate without manual trigger?

@AccessAnalytic 2 жыл бұрын

I’m not sure it’s possible with Power Automate desktop. I have used 3rd party software called Power Update to automate refreshes in the past. poweronbi.com/schedule-power-bi-update-with-power-on/

@chandniiyer4720 3 ай бұрын

Hi, thank you very much for this video. This is really helpful for me! I just wanted to check about the workaround you mentioned in renaming multiple columns instead of making the first row as headers. In which video have you covered it please ?

@AccessAnalytic 3 ай бұрын

Can you let me know what minute of the video I mention that

@robinsteyaert9901 2 жыл бұрын

Thanks for the clear explaination. I only have one question. Can you help me understand how a change (ex. Input of the smiley) in the "Transform Sample File" flows though in the "Transform File" Custom function? When I change something in the 'Transform Sample File", it does not change anything in the "Transform File" custom function. Thanks in advance, Robin

@AccessAnalytic 2 жыл бұрын

Hi Robin, make sure you don't make any change directly to the function otherwise it breaks the link. Otherwise in theory changing anything in the sample file will automatically update the function

@robinsteyaert9901 2 жыл бұрын

@@AccessAnalytic Thanks, that seems to solve the problem!

@chrishartnell8936 8 ай бұрын

Great video. When did the ability to get files from SharePoint folder appear in PQ? Great to see its on the options now and not have to use workarounds. One draw back appears to be the need to have access to the whole site. Ive just built a query that allows consolidation of project files but if those files are in a site that has multiple libraries that have restricted access and the PMO team only have access to one library on that site I presume the query wont work for them? So this powerful functionality has implications on the site architecture eg in this case I will need to create a PMO site which the PMO team can have full access to?

@AccessAnalytic 8 ай бұрын

It’s been there for many years. You may have to adopt the SharePoint.Folder approach if full path access is unavailable. That refresh is a lot slower though.

@melissagenthner2705 2 жыл бұрын

Great video. Although I had an issue with the "The key didn't match any rows in the table" so to resolve it I had to format all of my xlsx files as tables then it seemed to work ok.

@AccessAnalytic 2 жыл бұрын

Thanks, normally that issue means one of your excel sheets was named differently to the others or there was one empty sheet or some oddity in one of the files compared to the others

@khalidmajeed2886 2 жыл бұрын

sir,useful video for student of power query

@AccessAnalytic 2 жыл бұрын

Great, thanks for leaving a comment

@rachelfoulger9705 2 жыл бұрын

Hi This is one of the most helpful videos I've found so far, thank you! I was getting so confused why so many queries were created on the left hand side when importing from a folder on Power BI and this explains it all so clearly. One question I do have - do you know whether there is a way to combine only a select number of rows from each file that you're importing? I have a folder with a new spreadsheet of information for every day, for example, and I'd like to combine them but each day's spreadsheet has 25,000+ rows. I only actually need to import and combine the first 5,000 from every file and combine (as they are sorted to show the most important products in the top 5,000 rows and these are what we're interested in analysing). I've tried adding a 'FilterRows' parameter but with no success so I think I may be doing it wrong. If you have any answers that would be amazing. Thank you, Rachel

@AccessAnalytic 2 жыл бұрын

Glad you found this helpful Rachel. For your scenario I’d click on the sample file, then go to the Keep Rows button and choose Keep Top Rows the type 5000 in the box.

@mstone8001 Жыл бұрын

Super helpful! If there’s only 1 worksheet that contains a table, will this process ‘find it’ or do I have to specify the worksheet by name?

@AccessAnalytic Жыл бұрын

You have to specify which sheet or table and each file must have same sheet name or table name

@yasinacar8028 2 жыл бұрын

İt' s very useful video 👍👏

@AccessAnalytic 2 жыл бұрын

Glad you liked it Yasin

@PrestonFlyer Жыл бұрын

Another Excellent video! One question though when consolidating excel files from a sharePoint folder how do I get the consolidated file to include the File name of the source file - PQ does this when I consolidate from a local drive vis the Transform and Edit button - I need this as I use this to show trends in the data from version to version

@AccessAnalytic Жыл бұрын

In the comments there’s one from IanKR. Check it out

@lolitsajoke77 2 ай бұрын

Garcia got that CTE new Power

@iankr 3 жыл бұрын

Hi Wyn. Many thanks for this. I've managed to combine the files in a SharePoint folder with your helpful guidance here. Is there any way to add a new column to the Consolidation query that has the source filename for each row of data? I'm doing this in Power Query, and my Consolidation query doesn't have the Source Name column, which yours has retained. Not sure why.

@AccessAnalytic 3 жыл бұрын

Hi, in the Consolidation query created at around 14:20 there should be a Removed Other Columns step with a cog next to it. Maybe one of the columns being removed there for you is Source Name ?

@iankr 3 жыл бұрын

@@AccessAnalytic Many thanks for your response. I didn't have a chance to look at this today. I'll have a look tomorrow and let you know how I get on. Regards.

@realpulsecoin 3 жыл бұрын

Great content! From Sharepoint/Onedrive will also in Excel? Thanks for alle the tips 👍

@AccessAnalytic 3 жыл бұрын

It should providing you have the right version of Office 365 support.microsoft.com/en-us/office/power-query-data-sources-in-excel-versions-e9332067-8e49-46fc-97ff-f2e1bfa0cb16

@realpulsecoin 3 жыл бұрын

@@AccessAnalytic Great! Thanks again!

@ccrosetti12 2 жыл бұрын

Thanks so much! Would this work on a Data Lake folder as well?

@AccessAnalytic 2 жыл бұрын

Don’t know sorry, not sure how you navigate to to a data lake folder. You may need to build the custom function manually.

@tsetsegbat1920 Жыл бұрын

Thank you soo much for the super useful content. Can you please help me on the error when connecting multiple excel files on share points to Pbi. Some reason it won’t recognize my .Xlsx files keep giving pop up message it can’t recognize data format and use csv, txt, excel etc yet all of my data set is all excel in xlsx. Thank you soo much in advance.

@AccessAnalytic Жыл бұрын

Hi , there’s currently a bug that’s been fixed. Check you have the latest version of Power BI desktop installed Also check out my updated video: kzfaq.info/get/bejne/Y751aqt2v8i1mY0.html

@Grace-ws4td 2 жыл бұрын

Very clear vid, but I don't have the "sharepoint folder" option in the "Get data>From File" list - I believe it's not an option with 365 business. How do I get information from multiple files in a folder from my org's sharepoint?

@AccessAnalytic 2 жыл бұрын

Thanks Grace, I think the following code will work even though Business Version doesn't have the SharePoint Folder button = SharePoint.Contents("YOURTOP LEVELFOLDERPATH/", [ApiVersion = 15]) If you create a blank query and then replace the Source Step formula with this code it should work, then you get a list of Tables and click on the word Table next to Documents (normally around Row 6) and then keep on clicking each Table against each Folder to get to the right folder. Finally click on the double down arrow on the the first column called Content (when you can see all your files listed). That should trigger the folder consolidation. Note that your TOPLEVELFOLDERPATH will be something like mycomany.sharepoint.com/ with maybe one subsite name after the /. It's not the entire URL from your sharepoint folder.

@timodondino 2 жыл бұрын

Very cool video! 😊 I just have one question. If I understand it correctly, in both ways there is your individual name/account in inside the file path like in 6:34. In this case it is not possible for other users to click on the "refresh all data" button because they have access to the files but no access to my personal file path. How can I change that? In our company many people need to have refresh the data from the power query

@AccessAnalytic 2 жыл бұрын

Hi Timo V, you'd need to use the SharePoint option per 10:08 and ensure all users needing to refresh have access to the files/folders. You could also do it on OneDrive but that's not ideal for company reports as the OneDrive folder is removed when you leave an organisation

@timodondino 2 жыл бұрын

@@AccessAnalytic Thank you. Got it so far 😊 But it seems like you can just select files in the Editor. Is it possible to choose a certain folder and then it combines all the files in that folder every time I click on refresh?

@AccessAnalytic 2 жыл бұрын

Do you mean allow the user to pick which folder? You could do this with using a parameter for the folder name and embed that into your code

@amithmithu2005 Жыл бұрын

Thanks for the video.. I am able to extract data from nearly 1000+ folders, in different locations. Had to use sharepoint.files. Then formatted and consolidate it. But my resultant data set is large, but less that excel limit. My problem is I need the final output in an excel. The load time for an excel is really huge. I am spending hours to get the data, for each refresh. Any easier way to export data?

@AccessAnalytic Жыл бұрын

Maybe check this out kzfaq.info/get/bejne/Y751aqt2v8i1mY0.html but with that many folders and files it sounds like you should invest in a database

@JaniceCook-jx8pw Жыл бұрын

Great video that I was introduced to at the Global Excel Summit 2023. However, I don't have a 'From SharePoint' option displayed - do I need to activate this in some way (I have a full Business 365 licence) and SharePoint folders Sync'd. I used 'From Web' instead, and seemed to give the same options. I selected 'Organisational Account' and signed-in/Authenticated. but when I click 'Connect' I get "We couldn't authenticate with the credentials Provided". I get this continually, and have tried several accounts - same thing - what am I missing on the set-up front?

@AccessAnalytic Жыл бұрын

Bizarrely Business version doesn’t have the SharePoint button but the following code will work even though Business Version doesn't have the SharePoint Folder button = SharePoint.Contents("YOURTOP LEVELFOLDERPATH/", [ApiVersion = 15]) If you create a blank query and then replace the Source Step formula with this code it should work, then you get a list of Tables and click on the word Table next to Documents (normally around Row 6) and then keep on clicking each Table against each Folder to get to the right folder. Finally click on the double down arrow on the the first column called Content (when you can see all your files listed). That should trigger the folder consolidation. Note that your TOPLEVELFOLDERPATH will be something like mycomany.sharepoint.com/ with maybe one subsite name after the /. It's not the entire URL from your sharepoint folder.

@erinfirmat36 Жыл бұрын

Thanks Wyn! Your videos are incredibly clear and helpful. I'm still having an issue with the "Expression Error: Column 1 of the table wasn't found" problem though - I've been stumped for days about it and I got so excited when you mentioned the fix by deleting "Changed Type", but when I Close & Apply I still get the same error popping up. Any further suggestions for this? For reference - when I linked to these files in a local folder I didn't have this problem. I'm now trying to link to the exact same files in a SharePoint folder and having this issue. The only changes I'm making in Transform Sample File is to delete the first row and promote headers. I can see the column there - name spelling and capitalization is correct - but the error persists. Any guidance would be so appreciated. Many thanks!!

@AccessAnalytic Жыл бұрын

Simplest technique is to to add 1 file at a time to folder and refresh, see if issue, repeat adding 1 file at a time until you hit the error then check to see what’s different about that file

@erinfirmat36 Жыл бұрын

Well. This is the most "is it plugged in?" strategy you could have suggested and I was absolutely sure this would not identify my problem after everything I've tried... and then I found four entirely blank files in my folder that apparently had failed to pull properly. At least 12 hours of my life gone trying to solve this... but it's working now and I'll never make the same mistake again, and in the meantime I've learned a ton about data source connections, so there's that. 😅 Thank you!

@AccessAnalytic Жыл бұрын

@@erinfirmat36 Hah, thanks for coming back to me and letting me know you found a solution 😄. There is a less "brute force" approach to identifying the issue but it's too hard to explain in this chat format... I feel a future video in the making!!

@erinfirmat36 Жыл бұрын

@@AccessAnalytic I will get my popcorn ready!

@e.dejong6421 3 жыл бұрын

Thanks. Great vid. Do you know how i can get the file name of every file into a column? (A side the information that is already in every file?) Sometimes a date or name in the filename can help to determen what the sourcefile of that row of data was.

@AccessAnalytic 3 жыл бұрын

At around the 2 minute mark you can see the file names appear in column 1.

@e.dejong6421 3 жыл бұрын

Oh yea, source.file. Thanks again!

@e.dejong6421 3 жыл бұрын

@@AccessAnalytic Hello W, but when i combine files that are in a Sharepoint folder, i don't get the sourcename in a kolumn in the output. In your example i also don't see the source name. Do you know a solution for that? Thx!

@DiptaGhossan Жыл бұрын

Hey Wyn, i have my sharepoint folder synced to windows explorer and get my data from there, wont it work too? it worked for me but then my collagues tried to refresh the data, unfotunately it didnt succeed with message that the connection is under my name and not allowing her to refresh. she definitely have access to that folder as well. is that because of the getting daya from sync folder thing to explorer?

@AccessAnalytic Жыл бұрын

Hi, if you look at the Source step in Power Query you’ll see it’s referring to you C:Drive copy of the SharePoint folder. You need to connect to the online SharePoint version if you want others to refresh it.

@quentinlange2011 2 ай бұрын

Great video, you are referring to a video if we have a lot of columns for headers, I didn't find it. How should I do if I have a lot of columns, to not rename every one of them

@AccessAnalytic 2 ай бұрын

Not sure maybe this one ? How to use Power Query to Combine Multiple Files that have different headings kzfaq.info/get/bejne/Zp-kqcySxJ7dm30.html Can you let me know which minute of the video I mention that

@quentinlange2011 2 ай бұрын

@@AccessAnalytic I will watch thanks. You mention it at 4:35

@AccessAnalytic 2 ай бұрын

@quentinlange2011 - maybe this one then kzfaq.info/get/bejne/b5egqZyW3NfDeoE.html

@AccessAnalytic 2 ай бұрын

Maybe this then kzfaq.info/get/bejne/b5egqZyW3NfDeoE.html

@gospelmoto2833 Жыл бұрын

what office version do you use sir? Can I do what you did in ms office 2013? By the way, thanks for the tuts. Very helpful.

@AccessAnalytic Жыл бұрын

I’m using office 365 ( and the monthly release version ). You won’t be able to do this with Excel 2013. There is a limited Power Query add-in for Excel 2013

@melissagenthner2705 2 жыл бұрын

Can the files have different data? I need to combine 4 files each having a few columns I need. Should I transform instead of Combine so that I can use the Sample file for edits on each file then combine?

@AccessAnalytic 2 жыл бұрын

For the From Folder technique the files need the same columns of data and same structure. If the files are very different then then import the 4 individually, get them into the same shape then append them to form one table. Disable the load of the 4 "helper" queries and only load the appended table to Excel or the data model

@AnkitGupta-cn1zd 2 жыл бұрын

Hello, It was very informative video but the challenge I am facing with the share drive is, if that share drive folder is not mine but someone else shared that folder with me? Any help would be appreciated. Thank You!

@AccessAnalytic 2 жыл бұрын

As long as it's OneDrive for Business or SharePoint online and they have given you read access to the folder it should work ok

@AnkitGupta-cn1zd 2 жыл бұрын

@@AccessAnalytic Thanks a lot ! It seems I was able to access all the files once I login.

@zackcarter3634 2 жыл бұрын

What method would you use if you had to duplicate the Sample file into 2 seperate process because of the data structure and you wanted to merge and use the final combined process into each workbook?

@AccessAnalytic 2 жыл бұрын

Hi Zack I’d do 2 separate sets of From Folder and the append the results at the end and only load the appended file

@zackcarter3634 2 жыл бұрын

@@AccessAnalytic Thank you for the reply. This logical step was what I missed in what i was trying to accomplish

@AccessAnalytic 2 жыл бұрын

You’re welcome Zack

@vdn5716 2 жыл бұрын

Thanks I am having issue with combine , I have two files in SharePoint folder oct-21 and dec-21 oct-21 has data from 2020 till oct and dec has only 2021 data ..when i combine both data duplication is happening for columns which are present in both files ..other than that other columns are showing correct data. Please assist on this...

@AccessAnalytic 2 жыл бұрын

You'd need to add a remove duplicates step

@vdn5716 2 жыл бұрын

@@AccessAnalytic ok sir fine

@jerrydellasala7643 2 жыл бұрын

NOTE: In the latest Insider version (1/13/2022), the options once the folder is selected have changed. There are now 4 buttons: [Combine] drop-down, [Load] drop-down, [Transform Data] and [Cancel]. Using just the [Transform Data] button will just bring in the Source and not perform the operations shown here. Use the [Combine] -> Combine & Transform Data option to get the transformation shown here.

@AccessAnalytic 2 жыл бұрын

Thanks Jerry, I think that matches my screen screen at 01:36 ? That's the semi annual channel version.

@A5mis 2 жыл бұрын

I have a folder with 400k+ rows and this is very slow. I tried changing the file instead of the sample file, but they are both equally slow. Not sure if there's a better way to automate that?

@AccessAnalytic 2 жыл бұрын

Large files with lots of columns will be slower. How many files do you have and how long is it taking?

@ItsjustmeAR 2 жыл бұрын

I have to completely replace the files within the folder and it’s causing the PQ to break. Any tips for a solution? I believe my query is pointing to a specific file at the moment, so I will try it with the first file route. Looking for other suggestions if I can….

@AccessAnalytic 2 жыл бұрын

It shouldn’t break if your new files have exactly the same structure as the old files. I’d test with just 1 file in the folder and if that fails compare the column and sheet names between the one that works and the one that fails

@ItsjustmeAR 2 жыл бұрын

@@AccessAnalytic thanks! Will do!

@stephanweaver1960 3 жыл бұрын

It's a real struggle from SharePoibt for 900 files with a table with 100+ rows (100k data rows) It's rulling at 900 seconds. I had to incorporate vBA to make it an incremental update to a backup master ,. For only SP records that gave changed (which is not the same as file has changed).

@AccessAnalytic 3 жыл бұрын

Yes SharePoint is a lot slower. Sometimes using SharePoint.Contents is quicker , or creating staged consolidations. Eg 1 file is a consolidation of one year, then the final consolidates the years

@pranaykhobragade9659 Жыл бұрын

Hi Access Analytics, When I try to change share point files to share point contents, it asks for credentials and after putting it says credentials does not work. It works smoothly for SharePoint files? Any ideas why?

@AccessAnalytic Жыл бұрын

No that’s not something I’ve heard of before sorry. I’d guess something to do with permissions set up on SharePoint but I don’t know

@pranaykhobragade9659 Жыл бұрын

@@AccessAnalytic Thank you for your reply.

@jamesshi9993 2 жыл бұрын

I have a problem to left-out merge two excel files from the same folder via Power Query. I merged but each row doubled. How to solve this? Thanks.

@AccessAnalytic 2 жыл бұрын

You will have to run a remove duplicates on the key column of the file you are merging the new columns from prior to the merge

@JuanDiazSilvermyst 2 жыл бұрын

So how do we add headers like "Authors, tags, file size?" ?

@AccessAnalytic 2 жыл бұрын

If you go to the Removed Other Columns step on the consolidation query and click the cog next to it you can add back in certain fields including Attributes

@sachinnikale5291 Жыл бұрын

my pc directly opens the files application it does not ask to choose any path and neither iam seeing any excel file in those folders even though i have one

@AccessAnalytic Жыл бұрын

What version of Excel are you using ( you can check via File - Account - About- Excel

@lauryap 2 жыл бұрын

Hi, what if the files are kept in Sharepoint only for 3 months and I want the consolidate file to keep all data even though the first files will not be available anymore?

@AccessAnalytic 2 жыл бұрын

Hi Laury, the data has to be there for Power Query to consolidate it. Power Query cannot store historic data. One option would be to do a one off consolidation of historic data and load to an excel table so that future refreshes reference that file.

@lauryap 2 жыл бұрын

@@AccessAnalytic Thank you! Next step would be to automate a copy of the consolidation file as pure data, perhaps monthly (consolidate those monthly), make another copy yearly.

@allardvanpelt6767 5 ай бұрын

Hi Wyn, you refer in your video to DO NOT 'use the first row ...' and you will show this in another video. Can you please forward this video? thx in advance. and thx for your clear explanation of PQ. it is a great help for me. grt Allard

@AccessAnalytic 5 ай бұрын

Is that at point 4:40?

@allardvanpelt6767 5 ай бұрын

@@AccessAnalytic Hi, no starting at 4:00

@AccessAnalytic 5 ай бұрын

I don’t know if I ever did a specific video on that. The technique is: Add an index column (starts at 0 ) Add a conditional column saying IF INDEX = 0 then “Date” else [ Date Column ] Then remove the original date column and then Use First row as headers

@AccessAnalytic 4 ай бұрын

@@allardvanpelt6767 this one... kzfaq.info/get/bejne/aLOelal5krPYZXk.html

@allardvanpelt6767 4 ай бұрын

@@AccessAnalytic , thx for the reply. making the query 'dynamic' was the hard part ....for me as a simple financial/business controller :-). your video was very helpful. thx again.

@MrKamranhaider0 2 жыл бұрын

Sir is it work for google sheet?

@AccessAnalytic 2 жыл бұрын

No this is a Microsoft technology built into Excel and Power BI. I think there may be something similar in google sheets but don't know

@johnmarkham1912 Жыл бұрын

Tried this with a folder of Excel files, get an error message. “Unable to connect, We encountered an error while trying to connect. Details: file contains corrupted data”. All the files open correctly in Excel. Any suggestions?

@AccessAnalytic Жыл бұрын

Hmmm, not sure John, are they xls rather than xlsx? Are you using Power Query in Excel or Power BI. If Excel, which version?

@alibaroroh9355 Жыл бұрын

but why when we change source folder from drive c to drive d (which have same data n folder) and we refresh its error.. how fix it? tk

@AccessAnalytic Жыл бұрын

Make sure you change the Helper Queries too.. I think the Sample File might reference the drive

@alibaroroh9355 Жыл бұрын

@@AccessAnalytic its doesnt work

@AccessAnalytic Жыл бұрын

@@alibaroroh9355 Too many potential problems to debug here sorry. I'd suggest posting some screenshots of where the Power Query break. Either to here: www.reddit.com/r/PowerBI/ or to techcommunity.microsoft.com/t5/microsoft-excel/ct-p/Excel_Cat

@OviWanKeno9i 2 жыл бұрын

Combine and Transform gives me an error. I have 70 files to merge and get a few different parameters to pick from. Each file has 1-15 tabs or so. I made the structure the same, so it should be okay.

@AccessAnalytic 2 жыл бұрын

You can only easily combine 1 sheet from each file, it gets complicated to do multiple sheets. There's often an issue where the the sheet name of one file is slightly different or the column heading has a space or something simple but easy to miss

@phoolkhan87 2 жыл бұрын

Excellent ... I have some problem for which your guidance is requested: *I am using excel files (monthly basis) as get data from folder option *All excel files have same pattern (for info only) * Each workbook have 12 sheets (some times there are more than 20 sheets) *Each file have first four rows and at 3rd row date is mention.(sys run date: 31-JAN-2022) in one cell *Below the that date there are transactional columns Suppose have following pattern: 1row:FBL company limited 2row: blank 3row: Sys run date: 31-JAN-2022 4row: blank 5row: haves headers How can I get the date from such situation?

@AccessAnalytic 2 жыл бұрын

That sounds like a complex time consuming scenario to investigate. You may get some help at the Excel Tech Community if you can add samples / screenshots and simplify it techcommunity.microsoft.com/t5/excel/ct-p/Excel_Cat

@DiptaGhossan Жыл бұрын

When my load failed with message "[Expression.error] They key didnt match any rows in the table, what does it mean?

@AccessAnalytic Жыл бұрын

One of your files might have an inconsistent sheet name or headings. I’ve also seen odd messages when files have #Ref errors in them

@DiptaGhossan Жыл бұрын

@@AccessAnalytic found it. it seems that my two files in the folder have different table names and thus when my source file take Table, it doesnt recognize the name of second table. apparently i can fix it with deleting the expression that calls out table name/sheet name with 0 in the transform file

@AccessAnalytic Жыл бұрын

Glad you solved it

@meehere 3 жыл бұрын

I don't see the option "From SharePoint Folder" in my Office 365. Is that because its still in beta version?

@AccessAnalytic 3 жыл бұрын

Not all versions of O365 contains the SharePoint folder connector unfortunately. I've been campaigning for this to be fixed but forgot to mention that here sorry. What version do you have?

@meehere 3 жыл бұрын

@@AccessAnalytic I am on version 2120 (Build 13628.20380)

@AccessAnalytic 3 жыл бұрын

Under File > Account look to see what it says up the top right. Mine says Subscription Product Microsoft 365 Apps for enterprise

@meehere 3 жыл бұрын

@@AccessAnalytic Thanks for checking. Perhaps that explains the difference. Mine is Microsoft 365 Apps for Business

@AccessAnalytic 3 жыл бұрын

Ah shame, I’ve raised a user voice item, please vote excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/42842856-add-sharepoint-folder-connector-to-m365-business-a