Combining Multiple Files from a folder using Power Query in Excel or Power BI ( ⚠️see description )
ฝัง
- เผยแพร่เมื่อ 3 ส.ค. 2024
- ⚠️ 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/ - แนวปฏิบัติและการใช้ชีวิต
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!
Fantastic Alex, glad to help, thanks for leaving a comment and letting me know you liked it.
Hi, no starting at 4:00
sorry comment :-)
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!
Thanks dee. We're on an accelerating trajectory! Share the word. 😁
Best explanation of files in folders I’ve found on YT. THANK YOU!
Thank you very much Bob. I appreciate you taking the time to leave a comment.
Oh you help me a lot .Before this I had to build customs function for transformation.
Glad tp help Kamran
I like this guy. easy to understand. No nonsense. Very explanatory.
Thanks, this guy is grateful for the kind comment 😀
Helped me a lot to understand the principles of Power Query. Very well explained and easy to follow. Brilliant! Thank you so much!
You’re welcome. Thanks for taking the time to leave a kind comment
My go-to guy! Precisely what I needed. Thank you, Wyn!
Cheers, make sure you check out the updated version of this video too. Link is in the description
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
Glad it helped!
Tks so much for the detailed video! I just combined 10 files with Power Query !
Awesome!
You will never know how much you helped me with this video. Many thanks and God bless you real good.
Thank you very much, glad it helped
I finally understood what was going on in there. Thanks Wyn.
Thanks for taking the time to leave a kind comment Nick
You can do it in many ways, but this was really lean och quick! The best way! Many thanks!!
You’re welcome
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.
Thanks, hopefully the sound has improved in my more recent videos. This was recorded few years ago
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.
Thank you Grainne D
Thanks Wyn, you have saved me again 👍
No worries Brad, thanks for letting me know you're finding these useful
Thank you so much! I was struggling on combine and transform some pdf's and this video made my life so easy haha!
Glad to help, thanks for taking the time to leave a comment and letting me know
Great vid Wyn!
Thanks for explaining step-by-step n the differences between the two approaches
No worries Yasir
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"!
You’re welcome Victor, thanks for your comment
What a GREAT explanation! Very clear, conscience and in plain terms - I can now properly move forward with my Power BI Power Query, thanks 🙂
You’re welcome. Thanks for the kind feedback
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.
Thanks for the feedback Marcus
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!!
You're welcome Jason. Thanks for taking the time to let me know it helped. Much appreciated.
Thanks for the perfect and paused explanation.
You're very welcome
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!!
That’s great Tim, thanks for letting me know
Thank You So Much, This is really very useful.
You’re welcome Vishal, thanks for taking the time to leave a comment.
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...
Excellent, glad to help Venkat
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 :)
Thank you so much Hesham 😀
Awesome, thank you very much, you saved me!
I appreciate you taking the time to let me know you found it useful. Cheers
Thanks for doing this video it is really helpful .
No worries Jacob - thanks for letting me know
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.
Thanks for taking the time to leave a kind comment Priya
Thanks for sharing Wyn! Very clear and detailed 👍
Cheers, I appreciate you leaving the feedback
Thanks so much for explaining this transformation. You really simplified it for me. :)
Thank you for taking the time to leave a comment Maria
Very nice video! thank for explaining all those intermediate steps!
You’re welcome Bart
Exactly what I was looking for, thanks!
Great, thanks for letting me know
This is fantastic! Thank you. This also helped me learn about parameters
Great! Thanks for letting me know
Thank you, pretty informative!
Cheers Amr
Brilliant explanation. Thank you Wyn!!!
Cheers Iván
Excellent! used it before, but never realized how it can be improve the overall process
Glad it was useful Vasif
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. 🤣
😳😆
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).
That’s great Luke!
@@perthling which training were you signing up for?
Very clearly explained. Thank you sir!
You're welcome Hassan, thanks for the kind comment
Thank you! You really saved me at work today!
You're welcome Robert, thanks for taking the time to let us know we've helped
Another great video. Thank you!
Cheers Mariusz
sir,useful video for student of power query
Great, thanks for leaving a comment
Very useful and simple explanation
Thank you Mayank
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
Great Lee! Episode 1 is out now 😀
@@AccessAnalytic oooh thanks! I didn't even check 🤦
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,
Thanks
Great. Thank you!
You’re welcome Luciano
Super helpful. I was wondering what those autogenerated steps were doing and how they were created in an existing query I was looking at.
Glad it was helpful!
Garcia got that CTE new Power
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!
That’s great Terri 😁. Thanks for letting me know
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
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.
İt' s very useful video 👍👏
Glad you liked it Yasin
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.
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
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?
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 ?
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 ?
Can you let me know what minute of the video I mention that
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!
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.
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.
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
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
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
)
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
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.
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?
You can apply a filter in one of the first few steps.
Great content! From Sharepoint/Onedrive will also in Excel?
Thanks for alle the tips 👍
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
@@AccessAnalytic Great! Thanks again!
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?
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.
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
You’re welcome. It should work fine.
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
In the comments there’s one from IanKR. Check it out
Thanks so much! Would this work on a Data Lake folder as well?
Don’t know sorry, not sure how you navigate to to a data lake folder. You may need to build the custom function manually.
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.
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.
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?
You have to specify which sheet or table and each file must have same sheet name or table name
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?
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/
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
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
@@AccessAnalytic Thanks, that seems to solve the problem!
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.
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:
th-cam.com/video/-XE7HEZbQiY/w-d-xo.html
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.
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
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?
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.
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.
At around the 2 minute mark you can see the file names appear in column 1.
Oh yea, source.file. Thanks again!
@@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!
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!!
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
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!
@@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!!
@@AccessAnalytic I will get my popcorn ready!
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.
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 ?
@@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.
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
Not sure maybe this one ?
How to use Power Query to Combine Multiple Files that have different headings
th-cam.com/video/09tvia_8ykI/w-d-xo.html
Can you let me know which minute of the video I mention that
@@AccessAnalytic I will watch thanks. You mention it at 4:35
@quentinlange2011 - maybe this one then th-cam.com/video/91pv9ewq_JM/w-d-xo.html
Maybe this then th-cam.com/video/91pv9ewq_JM/w-d-xo.html
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
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
@@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?
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
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.
Thanks Jerry, I think that matches my screen screen at 01:36 ? That's the semi annual channel version.
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?
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
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!
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
@@AccessAnalytic Thanks a lot ! It seems I was able to access all the files once I login.
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?
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.
I’m running into some issues and I think it is because the files I am combining have a varying number of columns. My reports are property comparisons of income statements for a single month so the files I’m combining are other monthly reports. We close and open new locations so that explains the varying column numbers. Would you happen to have any advice on dealing with varying number of columns? Please and thank you in advance!
P.S. Your videos are amazing!
Maybe this video: How to use Power Query to Combine Multiple Files that have different headings
th-cam.com/video/09tvia_8ykI/w-d-xo.html
But there may be better approaches, what’s in the columns that differ in number? Are there always common columns that do line up?
@@AccessAnalytic yes the first couple of them will since the centers are in alphabetical order. So it’s really just depends on the name of a new center or a closed center.
@jarmandomelgoza2149 if each column represents a different cost centre then sounds like you might need to unpivot other columns in the transform sample file step
@@AccessAnalytic yes that’s one of the steps that I undergo once I promote the centers all the way to become headers and do some other changes in between.
In that case the different column names ( if the are the unpivotted ones ) shouldn’t cause a problem.
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?
Maybe check this out th-cam.com/video/-XE7HEZbQiY/w-d-xo.html but with that many folders and files it sounds like you should invest in a database
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
Is that at point 4:40?
@@AccessAnalytic Hi, no starting at 4:00
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
@@allardvanpelt6767 this one... th-cam.com/video/2MnbFH-Mt5E/w-d-xo.html
@@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.
Thank you ! Please help me. I want to combine multiple excel. I followed your but I have faced some error
You’re welcome.
I’d suggest posting the issue to techcommunity.microsoft.com/t5/microsoft-excel/ct-p/Excel_Cat?WT.mc_id=M365-MVP-5002589
Or
Https://www.reddit.com/r/excel/
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?
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
@@AccessAnalytic Thank you for the reply. This logical step was what I missed in what i was trying to accomplish
You’re welcome Zack
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?
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.
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?
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
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...
You'd need to add a remove duplicates step
@@AccessAnalytic ok sir fine
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).
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
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?
Large files with lots of columns will be slower. How many files do you have and how long is it taking?
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….
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
@@AccessAnalytic thanks! Will do!
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.
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
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.
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
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?
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
@@AccessAnalytic Thank you for your reply.
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
What version of Excel are you using ( you can check via File - Account - About- Excel
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?
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.
@@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.
Sir is it work for google sheet?
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
So how do we add headers like "Authors, tags, file size?" ?
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
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?
Hmmm, not sure John, are they xls rather than xlsx? Are you using Power Query in Excel or Power BI. If Excel, which version?
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
Make sure you change the Helper Queries too.. I think the Sample File might reference the drive
@@AccessAnalytic its doesnt work
@@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