How to use Power Query to Combine Multiple Files that have different headings
HTML-код
- Опубликовано: 19 июн 2024
- When using Power Query to Combine Files from a folder there is a problem that only the 1st file's headings are used in the output. If a later file has a new heading it won't appear.
1 line of code courtesy of Gil Ravi fixes this
= List.Union (List.Transform( PreExpand[Transform File], each Table.ColumnNames(_)) )
Here's Gil's post on this
datachant.com/2017/02/07/powe...
And a post relating to changing column types without referencing column names:
datachant.com/2016/10/07/robu...
Gil's book: www.amazon.com/Collect-Combin...
Download the files:
aasolutions.sharepoint.com/:f...
Good option suggested from Muneer Marzouq on LinkedIn is that after drilling down you could just wrap the formula in Table.Combine( ) . The downside is you lose the source file names - the upside is it's easier to remember 😁
Connect with me
wyn.bio.link
accessanalytic.com.au/ 
Хобби
Brilliant! I wish there was something better than like and save in RUclips - I need flashing lights a d pointing fingers - I know I need to come back to this video often! ➡️ ➡️ 📌📌📌⬅️ ⬅️❗
😁
There is a save button hidden in the 3 dots. You can create a playlist and save the video in it. This video is added to my Excel playlist :)
I found your RUclips channel after listening to your podcasts, so excellent that you’re sharing all this info in a great tutorial. I’m new to Power products, only started learning a month ago, but using everything in anger at work, replacing all my vba macros!!
Great to hear. Thanks for letting me know you’re finding things useful here
Amazing! I spent three hours researching until past midnight, and here you are with the simplest straight solution to the scenario :)
Glad I could help 😀
Thank you! Until today I hadn't realised that one could bunny-hop references to earlier points in the applied steps list and effectively get multiple bites at the data. So useful.
I remember being happily surprised on learning that technique too 😀
Wyn, I cannot thank you enough for sharing this fantastic video and your incredible knowledge of Microsoft Excel, Power Query, Power BI, and DAX! Your expertise and passion for these tools truly shine through in your content. I've had the pleasure of attending a couple of your workshops, and I must say, your insights have been game-changing for me. Your dedication to empowering others with these skills is genuinely inspiring, and I am incredibly grateful for the opportunity to learn from you. Keep up the amazing work, and I eagerly await your next masterpiece!
Thank you for the support Shagun. Greatly appreciated 🙏🏼
That's my whole weekend's trouble shooting why my queries are not pulling correctly explained in 9min...Thank you Sir
You’re welcome. Thanks for taking the time to leave a kind comment
Brilliant. Just what I need - not every day in the week, but pretty often. Mega thanks.
Glad to help, thanks for taking the time to leave a kind comment
Grateful does not even begin to express how I feel! I've been seeking this solution for longer than I care to admit. THANK YOU! I receive multiple reports that have the same columns but in a different order and this was a perfect append solution!
Hi, glad I could help. if the columns have the same names then a normal consolidation from folder process should work fine. The order shouldn’t matter.
Hi Wyn. Great new trick for the Power Query tool bag! Thanks for showing the steps and sharing the sample files to follow along. Much appreciated! I'll definitely bookmark this for future reference. Thanks for sharing and thumbs up!!
Thanks as always Wayne ⭐
Hey Wyn,
I used 1 line of code which emancipated me from recursive day/nightmares. Thank you again for sharing your knowledge and giving everyone a very good foundation.
Kind Regards,
Bhavik
Awesome. You’re welcome Bhavik
Thank you Wyn. This is another great technique to add to my ever-growing library of solutions!
No worries Shirley
Precious gem in my mini PQ formulas library! :)
Many thanks for this and for clear and simply explanations - great job!
Glad to help
You save my day. You're a brilliant man who can share the simplest ways. I love you man!
Awesome 😊
Thanks Wyn, I have been trying to find this process for a long time. Legend mate
You're welcome Tony 😁
Such a complicated issue solved with so much ease. Thanks a million !!
You’re welcome
Pure gold dust. Love it! Please dont stop sharing your content. I appreciate your succinct and clean approach. Diolch from Newcastle 🤓
P.s. this method can also be used to dynamically rename column headers AND dynamically format datatypes.
PowerQuery is the gift that kerps on giving.
Diolch yn fawr Imran. Agreed, Power Query is a well of goodness
Could you expand on how you dynamically format the datatypes?
I’ve not seen a simple method to do that. You could try this datachant.com/2018/05/14/automatic-detection-of-column-types-in-powerquery/
This just saved me many hours of work. Thank you!
You’re welcome. Thanks for taking the time to leave a your comment
After DAYS of looking for help, this is the first video that's gotten me some. Unfortunately, when I get to the end, everything's good, except for one last column that shows null. Then, when I load, it shows what the null columns SHOULD'VE been, but they're repeats. Still the closest I've gotten to what I need though, so I'm super grateful.
null columns may mean slightly different different spelling of column names ( spaces, uppercase etc )
Dear, Wyn! Thank you for the video! Great staff! Helped me a lot.
Thanks great to know
I can't believe i didn't find your channel before, what a pitty that i needed excel global summit to learn about the great wyn hopkins !
Hah, thanks Anthony, glad you found me!
This seems like a good alternative for append tables and delete nulls.
Thanks for sharing. You got a subscriber.
Great
Awesome....I have been looking for this solution for years. Thanks so very much
You’re welcome
I've been stuck with my assignment for weeks. This video is a life saver. Thank you so much.
No worries. Thanks for taking the time to leave a kind comment
@@AccessAnalytic my only problem was the pertinent values of most columns that's been combined were replaced with null. Do you have a video that can restore the missing values? Thanks again.
Hi it won’t replace with nulls, if the columns have slightly different names they will show up side by side so you would need to scroll down to see the values ( e.g. the new columns are offset to the right AND the data appears on NEW rows )
@@AccessAnalytic thank you very much!
This worked out great. Thanks so much for the walkthrough.
I appreciate you taking the time to let me know you found it useful
Very useful! I will keep the formula for the future reference
Useful explanation, breakdown into steps is helpful and thanks for crediting Gil Raviv.
Thanks
Amazing video as usually!
Today I was struggling with X folders and files there, getting proper structure and so on...seeing this, I will be smarter tomorrow with all the columns I need =)
Thank you. Glad to help and thank for letting me know.
Gil Raviv is the mutt's nutts!!! His book and blog are a must for anyone using Power Query. Thank you for making the video!!!
No worries, check out my interview with Gil here ruclips.net/video/07zOX5IYImI/видео.html
SUPER, thanks Wyn. I didn't know before.
Glad to pass on the tips Norbert
The easiest way I’ve seen. Thank you!
You're welcome Mariusz
This is just what I needed. You are a life saver. Just subscribed to your channel! Appreciate it!
Glad to help Gabby.
That's a life saver. Amazing content as usual.👍
Happy to help!
Brilliant. Just in time. Thanks for sharing.
Thanks for commenting 😁
Thank you so much! All my columns are now visible
That’s great, thanks for letting me know it helped
Learning from Brazil...
Thank you!
You're welcome
Excellent. A great use of M functions 💯👍
Thank you
Thank you for sharing this great method... that is so helpful
Glad to help Zuhair
Really nice trick Wyn. Thanks for sharing!
When I first saw the name I immediately thought of an Unpivot Other Columns based on the first file … but this is really cool too 😜
Yep unpivotting the transform file would be my first choice approach. A recent scenario needed the data loaded as columns as part of another process so unpvotting wasn’t an option
subscribed. Finally got me to stop adding a custom column to find tables within workbooks.
Great!
Wow.....this is so brilliant, been searching since only to come across. This is so helpful
Although the codes are not gonna be easy to remember but I can always refer to this video.
Thank you 👍
You’re welcome. I hardly remember any code these days!😄
Wyn thanks for this golden video, I think this is the best technique I have seen on this issue.
Very kind of you to take the time to say so. Cheers!
Very Good. Cheers from Brazil
Thanks Felipe
This is freaking brilliant! Thanks! Life's hard enough, this HELPS me a LOT! LOL! Thanks again!
Glad to help
You are awesome Sir
Thanku and lots of love from India 🥳🥳🎁
😀 Thanks for taking the time to leave a kind comment
Beautiful. I've a query with this problem. Now I think I can make it work and check some 700,000 records with some formulas.
Thanks!
Thanks for taking the time to leave a kind comment
Thank you. This was very helpful. Just to add, I found that I had to wrap this in a List.Buffer function as my query was taking too long to run based on the number of different columns I had. This solved the issue and it ran much faster.
Great, thanks for the "heads up"
Can you post the updated line of code here for me to refer others to, cheers!
For anyone who's interested, its something like HEADINGS = List.Buffer(... existing code). It's much faster. But I don't know if there's any drawback.
Nope , all good. Thanks for posting.
Super. Thats what I needed..
Thank you
You’re welcome
This is what I'm looking for.. tq for sharing..
You're welcome
You know I’m making a list of all your RUclips videos rather than writing that down!
Beautiful indeed, thanks Wyn!
Cheers Fernando
Thank you!! i learnt something new ....
Great, glad to help
Great Video, really very helpful. Best Wishes
Thanks Vishal, very kind
Great that's what I am looking for. Thumbs up
Excellent
Thank you!!!! Brilliant stuff.
Cheers Chris
Instead of "combine and transform", i go to "transform data"=> get table from binary=> expand table. This gets all the unique columns from all the files.
True ! ❤️
Sparkly. One line of code, many problems solved.
Thank you Wyn!!!! Just what I needed!
😄 Bill ? 😊
@@AccessAnalytic Sorry!!! 🤣🤣🤣
😆
Great trick, very useful. Thank you very much¡¡¡¡
Cheers Arnau
That's the man I was looking for.
Glad to help
Amazing. I didn't know that was possible. TVM.
You’re welcome
This is great! Thank you!
You're welcome
You just save me!!!! Thank you so much for this video ahhhh God bless you !
You're very welcome, thanks for letting me know it was useful
Thanks a million! Amazing as ussual
Glad to help. Thanks for taking the time to leave a kind comment
Brilliant. Thank you!
You’re welcome 😀
Great, Very useful, thanks
Glad it was useful, thanks for taking the time to leave a kind comment
Insane man thanks
No worries
Thank you so much!
You’re welcome
Honestly, for me the step before the trick (the column driven drill down) was the missing link.
The trick line itself wasn’t that bad.
Recently I had a similar problem that I solved, where I didn’t need List.Union but rather List.Combine.
Getting the column names, filtering them, replacing the headers with the actual columns, combining them into a single column.
Repeat for different filter values. Combining the results into columns of a table and move on from there…
This here is a great lesson, though, because it teaches us to leave the autopilot and start thinking for ourselves.
PS: I never use the Files From Folder input technique: it creates too many queries IMO. I prefer putting the path of the folder in a table, load that to PQ and work from there, doing every step myself. Any thoughts on that? Do you see any disadvantages for that compared to the std. interface approach? Thanks.
When consolidating from SharePoint I go via the transform option, make a master Folder query, reference that then do the the combine, this creates a sample file and consolidation both linked to the master folder query. Simpler to then change folders in future. I’m a fan of the transform sample file element generated by the UI
Much appreciated ^^
No worries Hendrick
Amazing trick ..
Thanks
THANK YOU!!
You’re welcome
Brilliant!!!
Thanks!
Thank you very much
You're welcome
Thank you
No worries
Are you a wizard? Because this is MAGIC!! Thank YOU!
Cheers Darren, check out my podcast / RUclips series Power Query Magic 😄 ruclips.net/user/PowerQueryMagic
thank you sir
You’re welcome
Thank you for the video. This solved most of my issues, but I had an issue with loading data because the Sheet Names were different. Found a workaround elsewhere by changing the formula of Transform Sample File>Navigation to =Source{0}[Data]. I get what it's doing, but now I think I'd like to know more about what the 'Helper Queries' are doing.
Hi, you might find this video useful : Combining Multiple Files from a folder using Power Query in Excel or Power BI
ruclips.net/video/nPlrQUbEn4o/видео.html
AWESOME!
Cheers
Thanks! Just what I needed. Also got me thinking: if I knew that my files would only ever have different permutations of columns "Measurement1" to "Measurement6", could I create a dummy file in the folder with just these headings and no data then use it as the sample file? It wouldn't dynamically accommodate further columns but, with over 200 Excel files to combine and no new columns for the foreseeable future, it might work in my situation.
Yep that should work
This is a great video, suppose next month some more headers are added or some are renamed still this formula can handle situation?
Absolutely
GENIUUUUUUUUUUUUUS THANKS!!!!!!!!!!!!!!!!!!
You’re welcome
brilliant
Thanks Lotfy
Thumb up 👍
Thank you Thank you Thank you Thank you Thank you Thank you Thank you Thank you Thank you Thank you
You’re welcome 😀
That's fantastic.
Thank you sooo much.
How can I do the same thing if there are multiple tables in multiple tabs in the same workbook file?
So do you want to consolidate multiple tables from multiple sheets from multiple workbooks, or just multiple sheets from 1 workbook? If it's just one workbook then check this out
ruclips.net/video/n8_sA6NMlkA/видео.htmlsi=H83zOfBiRPGWuBzU
OMG, this is amazing, Wyn; thank you so much for this clever post (and of course, a huge thanks to Gil!)
One problem though - when I use the exercise files, everything works perfectly.
However, I have a problem right away when I use my own files where my column headings are dates and my rows contain names. The combined files are loaded into PQ with column headers as follows: Column1, Column2, etc., as if PQ didn't know that my dates were intended as column headers. Am I doing something wrong or is there a (hopefully, easy) solution to this?
Thank you so much.
Thanks, Yeah if your headings are numerical / date it won’t auto promote headers. So you’ll need to click the Use First row as header button early in the transform sample file step
@@AccessAnalytic What I did was create a dummy file with 1 dummy row and 1 dummy col where the header is simply 'dummy', i.e. non-numeric. When I include that dummy file into the folder, then make that the "First file" for the combine, everything works perfectly! Somehow, the dummy file was ignored altogether saving me the effort to remove it myself.
What a wonderful service you've provided, Wyn. You are the real deal - THANK YOU SO MUCH.
Glad you got it working!
Thank you ! Great content ! I was wondering, what are the drawbacks in using the "Append" function in the Combine section in Home tab?
No downsides as such, appended table column names need to match exactly ( including upper / lower case match ) if you want the columns to stack on top of each other. Otherwise new columns are created
@@AccessAnalytic Great! Thank you! So the data from same name columns will be appended on top of each other. Any additional or different name column will be added as a new column. These new column will have null values corresponding to the data set where these do not exist.
@@sulemanharoon - correct 😀
@@AccessAnalytic Thank you !
You and your videos are always a great help !
Hi Whyn ,thanks for sharing your knowledge. In case the headers started in different positions (row 3, 4, 7) in each sheets and rows had to be canceled to promote as headers, what would the process be like, could you help me?
You'll need to apply some logic in the Transform Sample file step (that logic will be applied to each file ). Maybe you can apply a filter to remove the required rows, rather than specifying a specific count
İt's easy but literally life saver
Great to know, thanks Alpay
Is there a way to adjust this for cases when you need to remove some rows and promote the first row to headers?
You should be able to do that in the Transform Ssmple file query. ruclips.net/video/nPlrQUbEn4o/видео.html
Table.Combine does the same thing without writing code. but this is good learning knowledge
Yep you do then lose the file names, but if you don't care about those then that's a good option.
Does this work with CSV files? None of mine have tables and column names already.
Hi, yes it loves CSV files and converts them into Tables and columns
Thanks. The files I have contain some cells filled with a colour that I would like to retain in the combined file (not via conditional formatting). Is there a way?
Not using Power Query
Hello Whyn, is this possible to do when you combine sheets in an excel electronic book? thank you that you are very well, I send you a big hug!
Same concept should apply Osiel. I have a video on consolidating worksheets here ruclips.net/video/cPN24NK3_68/видео.html
Thank you for uploading your wonderful work. I am facing problem with Table 2 and Table 3. It's showing error. Difference is my file is CSV file. Do you have any suggestion?
To difficult to say without seeing sorry, maybe post an example here techcommunity.microsoft.com/t5/microsoft-excel/ct-p/Excel_Cat
Why PQ didnt pull in all the headers in the editor from the folder? Is there a limitation of columns in the editor?
No limit, in this scenario (when consolidating files from a folder) it just pulls the headers from the first file when doing the expand.
Awesome video..👏 Do we have a function to combine tables please assist 🙏
Table.Combine ?
Hi Wyn, love it, I've been tearing my hair out with this problem but ... can I get it to work when the files have multiple sheets with inconsistent headings. Can it combine the headings within the sheets inside a workbook into a list of master headings and then combine the master headings from each book? ... Just tried to do it. Everything seemed to work OK and while many column headings are recognisable some are dubious showing as "f[xx]" or "column[xxx]". But went ahead anyway to try and create the table to load but it seems to have gone into an endless aggregating loop.
Not sure on that sorry
I'll probably never do it the old way again. What's neat and simultaneously annoying is that this M model can be pasted directly into PowerBI & likely set to automatically refresh if it's a SharePoint folder. You bypass SSIS/ADF altogether and have PBI Services do the work for just the model you choose to refresh. Two decades of work say that I *have* to bring the data into SQL and do magic things, but you really don't at this point. That era of needing a database to do smart models for business users is really & truly over, but the nostalgia is eternal.
Yes the game has changed rapidly in the last 6 or 7 years. Still lots of room for SQL databases in this world though 😊
Wow!
In my case, I would like to use this trick but my data has headers that need to be merged from two rows.
Forexample, in the case on Measurement 1, Measurement 2, etc; suppose the data has another word like "Total weight" in a row just above "Measurement...." for all files.
Any ideas on how this would work out?
Thank you very much
It’s difficult to answer here but sounds like a situation where demoting the header, transpose, fill down and then merge the 2 headings . Then transpose again