Combining Multiple Files from a folder using Power Query in Excel or Power BI ( ⚠️see description )
Vložit
- čas přidán 30. 07. 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/ - Jak na to + styl
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 :-)
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
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 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
Great vid Wyn!
Thanks for explaining step-by-step n the differences between the two approaches
No worries Yasir
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.
You can do it in many ways, but this was really lean och quick! The best way! Many thanks!!
You’re welcome
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
I like this guy. easy to understand. No nonsense. Very explanatory.
Thanks, this guy is grateful for the kind 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. 😁
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
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 😀
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
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
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
Thanks for the perfect and paused explanation.
You're very welcome
Tks so much for the detailed video! I just combined 10 files with Power Query !
Awesome!
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
Brilliant explanation. Thank you Wyn!!!
Cheers Iván
I finally understood what was going on in there. Thanks Wyn.
Thanks for taking the time to leave a kind comment Nick
Very nice video! thank for explaining all those intermediate steps!
You’re welcome Bart
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!
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
Glad it was helpful. Thanks for letting me know.
Exactly what I was looking for, thanks!
Great, thanks for letting me know
Very clearly explained. Thank you sir!
You're welcome Hassan, thanks for the kind comment
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?
This is fantastic! Thank you. This also helped me learn about parameters
Great! Thanks for letting me know
Thank you, pretty informative!
Cheers Amr
Another great video. Thank you!
Cheers Mariusz
Thank You So Much, This is really very useful.
You’re welcome Vishal, thanks for taking the time to leave a comment.
Excellent! used it before, but never realized how it can be improve the overall process
Glad it was useful Vasif
Thanks Wyn, you have saved me again 👍
No worries Brad, thanks for letting me know you're finding these useful
Awesome, thank you very much, you saved me!
I appreciate you taking the time to let me know you found it useful. Cheers
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
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 🤦
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
Very useful and simple explanation
Thank you Mayank
Great. Thank you!
You’re welcome Luciano
Oh you help me a lot .Before this I had to build customs function for transformation.
Glad tp help Kamran
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
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
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.
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
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!
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
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.
İt' s very useful video 👍👏
Glad you liked it Yasin
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.
sir,useful video for student of power query
Great, thanks for leaving a comment
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.
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.
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/
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, 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
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.
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 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.
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!
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.
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
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.
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:
czcams.com/video/-XE7HEZbQiY/video.html
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!
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
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 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 czcams.com/video/-XE7HEZbQiY/video.html but with that many folders and files it sounds like you should invest in a database
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!
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.
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
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!
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.
Garcia got that CTE new Power
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.
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
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
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.
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!
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
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
czcams.com/video/09tvia_8ykI/video.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 czcams.com/video/91pv9ewq_JM/video.html
Maybe this then czcams.com/video/91pv9ewq_JM/video.html
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.
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
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/
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.
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
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.
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
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
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?
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
czcams.com/video/09tvia_8ykI/video.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.
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... czcams.com/video/2MnbFH-Mt5E/video.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.
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
I don't see the option "From SharePoint Folder" in my Office 365. Is that because its still in beta version?
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?
@@AccessAnalytic I am on version 2120 (Build 13628.20380)
Under File > Account look to see what it says up the top right. Mine says
Subscription Product
Microsoft 365 Apps for enterprise
@@AccessAnalytic Thanks for checking. Perhaps that explains the difference. Mine is Microsoft 365 Apps for Business
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
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
When my load failed with message "[Expression.error] They key didnt match any rows in the table, what does it mean?
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
@@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
Glad you solved it