How to use Power Query to Combine Multiple Files that have different headings

[@GrainneDuggan_Excel]

Brilliant! I wish there was something better than like and save in KZbin - I need flashing lights a d pointing fingers - I know I need to come back to this video often! ➡️ ➡️ 📌📌📌⬅️ ⬅️❗

[@AccessAnalytic]

😁

[@casaketagarwal4333]

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

[@excelprodata5626]

Amazing! I spent three hours researching until past midnight, and here you are with the simplest straight solution to the scenario :)

[@AccessAnalytic]

Glad I could help 😀

[@DrDamoStrikesBack]

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.

[@AccessAnalytic]

I remember being happily surprised on learning that technique too 😀

[@ayandapeter1681]

That's my whole weekend's trouble shooting why my queries are not pulling correctly explained in 9min...Thank you Sir

[@AccessAnalytic]

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

[@Bhavik_Khatri]

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

[@AccessAnalytic]

Awesome. You’re welcome Bhavik

[@EricaDyson]

Brilliant. Just what I need - not every day in the week, but pretty often. Mega thanks.

[@AccessAnalytic]

Glad to help, thanks for taking the time to leave a kind comment

[@CAKimberlyLewis]

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!

[@AccessAnalytic]

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.

[@pauliboo2]

I found your KZbin 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!!

[@AccessAnalytic]

Great to hear. Thanks for letting me know you’re finding things useful here

[@UlyssesHaq]

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.

[@AccessAnalytic]

Diolch yn fawr Imran. Agreed, Power Query is a well of goodness

[@elizabethsweatman4681]

Could you expand on how you dynamically format the datatypes?

[@AccessAnalytic]

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/

[@Lyriks_]

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 !

[@AccessAnalytic]

Hah, thanks Anthony, glad you found me!

[@ivicanuck]

Thank you soooo much, you saved my life with this technique! 😊

[@AccessAnalytic]

Glad to help.

[@rauljimenez5485]

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

[@AccessAnalytic]

No worries, check out my interview with Gil here kzbin.info/www/bejne/ZmjdgItrfr58o6s

[@rosa3709]

You’re great! It’s a bit confusing at first but I'm learning. Thanks.

[@AccessAnalytic]

Cheers ☺️

[@sanjokbartaula2622]

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.

[@priyankchhajed1407]

True ! ❤️

[@shyamadasgupta9423]

That will not work here. Try it. Once you get table from binary, you will see that the Tables only have Sheet. No Table inside. EDIT: Once I added the Custom column TABLES beside BINARY, I expanded it. There I got another DATA column (with TABLES under it) that could be expanded again. I removed all other columns except this DATA column. Then CLOSE&APPLY. THAT WORKED BEAUTIFULLY! Are my steps correct? Is this exactly what you do?

[@ArleneH-cb8wt]

I've been stuck with my assignment for weeks. This video is a life saver. Thank you so much.

[@AccessAnalytic]

No worries. Thanks for taking the time to leave a kind comment

[@ArleneH-cb8wt]

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

[@AccessAnalytic]

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 )

[@ArleneH-cb8wt]

@@AccessAnalytic thank you very much!

[@MarcShanai]

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.

[@AccessAnalytic]

null columns may mean slightly different different spelling of column names ( spaces, uppercase etc )

[@damionc]

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.

[@AccessAnalytic]

Great, thanks for the "heads up"

[@AccessAnalytic]

Can you post the updated line of code here for me to refer others to, cheers!

[@hk_200k]

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.

[@AccessAnalytic]

Nope , all good. Thanks for posting.

[@yahoobolt]

You save my day. You're a brilliant man who can share the simplest ways. I love you man!

[@AccessAnalytic]

Awesome 😊

[@Koniogdy]

Precious gem in my mini PQ formulas library! :) Many thanks for this and for clear and simply explanations - great job!

[@AccessAnalytic]

Glad to help

[@joeswright86]

subscribed. Finally got me to stop adding a custom column to find tables within workbooks.

[@AccessAnalytic]

Great!

[@SharathAcharyaputtur]

Such a complicated issue solved with so much ease. Thanks a million !!

[@AccessAnalytic]

You’re welcome

[@pauliboo2]

You know I’m making a list of all your KZbin videos rather than writing that down!

[@wayneedmondson1065]

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

[@AccessAnalytic]

Thanks as always Wayne ⭐

[@khemkas]

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!

[@AccessAnalytic]

Thank you for the support Shagun. Greatly appreciated 🙏🏼

[@shirleymoreman6725]

Thank you Wyn. This is another great technique to add to my ever-growing library of solutions!

[@AccessAnalytic]

No worries Shirley

[@ΓιαννηςΜπας]

that was brilliant! thank so much, you saved me tons of hours with my payroll worksheet!

[@AccessAnalytic]

Glad to help. Thanks for taking the time to let me know

[@IvanCortinas_ES]

Sparkly. One line of code, many problems solved.

[@txreal2]

This seems like a good alternative for append tables and delete nulls. Thanks for sharing. You got a subscriber.

[@AccessAnalytic]

Great

[@adrianreboredamartinez1073]

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!

[@AccessAnalytic]

Thanks for taking the time to leave a kind comment

[@GeertDelmulle]

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.

[@AccessAnalytic]

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

[@tonynewnham5894]

Thanks Wyn, I have been trying to find this process for a long time. Legend mate

[@AccessAnalytic]

You're welcome Tony 😁

[@JohnSullivan1]

This just saved me many hours of work. Thank you!

[@AccessAnalytic]

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

[@lenzypetty9371]

Awesome....I have been looking for this solution for years. Thanks so very much

[@AccessAnalytic]

You’re welcome

[@mirrrvelll5164]

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

[@AccessAnalytic]

Thank you. Glad to help and thank for letting me know.

[@TashieTash]

Thank you so much! All my columns are now visible

[@AccessAnalytic]

That’s great, thanks for letting me know it helped

[@mnowako]

The easiest way I’ve seen. Thank you!

[@AccessAnalytic]

You're welcome Mariusz

[@sands7779]

Useful explanation, breakdown into steps is helpful and thanks for crediting Gil Raviv.

[@AccessAnalytic]

Thanks

[@vasilisa6128]

Dear, Wyn! Thank you for the video! Great staff! Helped me a lot.

[@AccessAnalytic]

Thanks great to know

[@CelsonFC]

Learning from Brazil... Thank you!

[@AccessAnalytic]

You're welcome

[@danielmpinga4102]

This worked out great. Thanks so much for the walkthrough.

[@AccessAnalytic]

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

[@felipesignorellireis7839]

Very Good. Cheers from Brazil

[@AccessAnalytic]

Thanks Felipe

[@Dev_Bartwal]

You are awesome Sir Thanku and lots of love from India 🥳🥳🎁

[@AccessAnalytic]

😀 Thanks for taking the time to leave a kind comment

[@norbertschmidt6532]

SUPER, thanks Wyn. I didn't know before.

[@AccessAnalytic]

Glad to pass on the tips Norbert

[@anasschehima5164]

Thanks a lot , that's really brilliant !

[@AccessAnalytic]

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

[@jeongwonc6462]

Very useful! I will keep the formula for the future reference

[@johnhackwood1568]

Wyn thanks for this golden video, I think this is the best technique I have seen on this issue.

[@AccessAnalytic]

Very kind of you to take the time to say so. Cheers!

[@suhaimiabdazais]

This is what I'm looking for.. tq for sharing..

[@AccessAnalytic]

You're welcome

[@olayemmydurotolorun7102]

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 👍

[@AccessAnalytic]

You’re welcome. I hardly remember any code these days!😄

[@SuperDuperHappyTime]

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.

[@AccessAnalytic]

Hi, you might find this video useful : Combining Multiple Files from a folder using Power Query in Excel or Power BI kzbin.info/www/bejne/pIHPo4SLl6qhatE

[@rrrraaaacccc80]

Excellent. A great use of M functions 💯👍

[@AccessAnalytic]

Thank you

[@erickelley7703]

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.

[@AccessAnalytic]

Yes the game has changed rapidly in the last 6 or 7 years. Still lots of room for SQL databases in this world though 😊

[@juliusbiliran]

This is freaking brilliant! Thanks! Life's hard enough, this HELPS me a LOT! LOL! Thanks again!

[@AccessAnalytic]

Glad to help

[@tarek.grisha]

Thank you Thank you Thank you Thank you Thank you Thank you Thank you Thank you Thank you Thank you

[@AccessAnalytic]

You’re welcome 😀

[@cristian.angyal]

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 😜

[@AccessAnalytic]

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

[@z.719]

Brilliant. Just in time. Thanks for sharing.

[@AccessAnalytic]

Thanks for commenting 😁

[@loserfireable]

This is just what I needed. You are a life saver. Just subscribed to your channel! Appreciate it!

[@AccessAnalytic]

Glad to help Gabby.

[@ExcelUnlocked]

That's a life saver. Amazing content as usual.👍

[@AccessAnalytic]

Happy to help!

[@zuhairalmutawa9941]

Thank you for sharing this great method... that is so helpful

[@AccessAnalytic]

Glad to help Zuhair

[@darrenhall2305]

Are you a wizard? Because this is MAGIC!! Thank YOU!

[@AccessAnalytic]

Cheers Darren, check out my podcast / KZbin series Power Query Magic 😄 kzbin.info

[@cjjr85]

Great that's what I am looking for. Thumbs up

[@AccessAnalytic]

Excellent

[@cosladacity1]

Table.Combine does the same thing without writing code. but this is good learning knowledge

[@AccessAnalytic]

Yep you do then lose the file names, but if you don't care about those then that's a good option.

[@zzota]

Amazing. I didn't know that was possible. TVM.

[@AccessAnalytic]

You’re welcome

[@tlee7028]

Thank you!! i learnt something new ....

[@AccessAnalytic]

Great, glad to help

[@4nnbnn]

You just save me!!!! Thank you so much for this video ahhhh God bless you !

[@AccessAnalytic]

You're very welcome, thanks for letting me know it was useful

[@Fernando_Calero]

Beautiful indeed, thanks Wyn!

[@AccessAnalytic]

Cheers Fernando

[@naresh200]

Super. Thats what I needed.. Thank you

[@AccessAnalytic]

You’re welcome

[@shamamajamz7103]

Thank you so much!

[@AccessAnalytic]

You’re welcome

[@vueittraining]

Thank you Wyn!!!! Just what I needed!

[@AccessAnalytic]

😄 Bill ? 😊

[@vueittraining]

@@AccessAnalytic Sorry!!! 🤣🤣🤣

[@AccessAnalytic]

😆

[@MrWish332]

Great Video, really very helpful. Best Wishes

[@AccessAnalytic]

Thanks Vishal, very kind

[@tutsecret499]

That's the man I was looking for.

[@AccessAnalytic]

Glad to help

[@ajayrathod7777]

This is a great video, suppose next month some more headers are added or some are renamed still this formula can handle situation?

[@AccessAnalytic]

Absolutely

[@shubhabratadey]

Beautiful

[@AccessAnalytic]

Cheers

[@baskis69]

Great trick, very useful. Thank you very much¡¡¡¡

[@AccessAnalytic]

Cheers Arnau

[@deeepika91]

THANK YOU!!

[@AccessAnalytic]

You’re welcome

[@datascienceworld3854]

Thank you

[@AccessAnalytic]

No worries

[@syedaneesdurez9880]

Amazing trick ..

[@AccessAnalytic]

Thanks

[@gini77oo]

GENIUUUUUUUUUUUUUS THANKS!!!!!!!!!!!!!!!!!!

[@AccessAnalytic]

You’re welcome

[@LexWil7]

Brilliant. Thank you!

[@AccessAnalytic]

You’re welcome 😀

[@ExcelWithChris]

Thank you!!!! Brilliant stuff.

[@AccessAnalytic]

Cheers Chris

[@Wastrelification]

Insane man thanks

[@AccessAnalytic]

No worries

[@Paquerette67]

This is great! Thank you!

[@AccessAnalytic]

You're welcome

[@robertrogowski2594]

Why not just Table.Combine( „#Removed Other Columns 1”[Transform file])? Should work as well.

[@AccessAnalytic]

Yep you do then lose the file names, but if you don't care about those then that's a good option

[@wiggumo]

So does that mean 3 excel files. No matter what and how many columns there, they will all consolidate?

[@AccessAnalytic]

Sure does Mo Lo

[@cheikhhakim5864]

thank you sir

[@AccessAnalytic]

You’re welcome

[@andrewgrant9782]

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.

[@AccessAnalytic]

Yep that should work

[@malchicken]

Much appreciated ^^

[@AccessAnalytic]

No worries Hendrick

[@thuytamdang3390]

Thanks a million! Amazing as ussual

[@AccessAnalytic]

Glad to help. Thanks for taking the time to leave a kind comment

[@vsrinivasan574]

Great, Very useful, thanks

[@AccessAnalytic]

Glad it was useful, thanks for taking the time to leave a kind comment

[@lasisiabolaji4402]

Such a wonderful video. However, I'm currently facing a challenge. After replacing the list of headers with the "Headings", it keeps loading without stopping. In fact, I had to abort the query as the expected result ought to be about 25,000 rows but the query process is showing about 1.5m rows before I abort the process.

[@lasisiabolaji4402]

fYI..I'm dealing with 69 columns in the List from the "Headings"

[@AccessAnalytic]

Sounds strange. Try with a much smaller sample ( 5 columns, 20 rows ) and see if it works ok. I’d recommend posting your question with screenshots to one of the forums such as community.powerbi.com/t5/Power-Query/bd-p/power-bi-services Or techcommunity.microsoft.com/t5/microsoft-excel/ct-p/Excel_Cat?WT.mc_id=M365-MVP-5002589 Or www.reddit.com/r/excel/? www.reddit.com/r/PowerBI/?

[@mehdiabid8324]

Brilliant, thanks a lot for the video, just one simple question, what if i need data of a column to be considered just one time (columns to be considered all but one time) Hope to hear from you Thanks another time

[@AccessAnalytic]

I don’t understand sorry

[@mehdiabid8324]

@AccessAnalytic no problem Here is an example: In my work i receive each week an Excel file that contains some data of the current week and four previous weeks (for example this week I will receive a column named wk-32 and other columns of four pevious weeks : wk-31 wk-30 wk-29 wk-28) Next week I Will receive (wk-33 wk-32 wk-31 wk-30 wk-29) I use the method you shared it was vers helpful but I need the data of one column to be considered Just one time, for example i have sum of column wk-29 is 5000 dollars if the other Excel file contains aalso wk-29 I need to have only 5000 dollars not 10000. I home I could explain more, Thanks a lot,

[@AccessAnalytic]

@@mehdiabid8324 - I would use the Transform Sample file step to Unpivot Other Columns so that the individual week columns are a single column rather than separate ones. You can then apply a remove duplicates. kzbin.info/www/bejne/e4TEoWmmqbtrnKssi=QtL9nMz4FoGXfhHs

[@mehdiabid8324]

Thanks a lot for your answer, that would really help.

[@lucianoelias8961]

AWESOME!

[@AccessAnalytic]

Cheers

[@cesarsaldana3429]

amazing!!!!

[@AccessAnalytic]

Cheers

[@vacilando86]

İt's easy but literally life saver

[@AccessAnalytic]

Great to know, thanks Alpay

[@m.i.drissboutriha7665]

brilliant

[@AccessAnalytic]

Thank you

[@TheBebwa]

This is great. I'm trying to figure out how to add a step that skips the first few rows on each excel sheet any ideas?

[@AccessAnalytic]

Use the Transform Sample File query to remove the first rows, then that automatically applies to each file

[@TheBebwa]

This worked. Thanks@@AccessAnalytic

[@AccessAnalytic]

Great, thanks for letting me know

[@jaruwanu]

Thank you very much

[@AccessAnalytic]

You're welcome

[@EslamAbdo1919]

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?

[@AccessAnalytic]

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 kzbin.info/www/bejne/pGnCpHRsg7KfoaMsi=H83zOfBiRPGWuBzU

[@ExcelWizard]

Great use of List.Union I use Table.Combine let Source = Folder.Files("D:\Source Files"), GetTable = Table.Combine(List.Combine(Table.AddColumn(Source, "Table", each Excel.Workbook([Content],true)[Data])[Table])) in GetTable And 2 more steps to add fileName let Source = Folder.Files("D:\Source Files"), GetTable = Table.ExpandListColumn(Table.AddColumn(Source, "Table", each Excel.Workbook([Content],true)[Data]),"Table"), AddFileName = Table.AddColumn(GetTable, "Table_fileName", each Table.ReorderColumns(Table.AddColumn([Table],"Name",(t) => [Name]),{"Name"} & Table.ColumnNames([Table]))), Combine = Table.Combine(AddFileName[Table_fileName]) in Combine

[@AccessAnalytic]

I like / prefer the Table.Combine approach - I used it here kzbin.info/www/bejne/rGiuZZ-kgb2hnqc

[@ExcelWizard]

@@AccessAnalytic Great challenge

[@AccessAnalytic]

@@ExcelWizard Thanks

[@sulemanharoon]

Thank you ! Great content ! I was wondering, what are the drawbacks in using the "Append" function in the Combine section in Home tab?

[@AccessAnalytic]

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

[@sulemanharoon]

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

[@AccessAnalytic]

@@sulemanharoon - correct 😀

[@sulemanharoon]

@@AccessAnalytic Thank you ! You and your videos are always a great help !

[@ChrisSmithFW]

Great solution if starting a new query but not so great if the query has been built already since removing all subsequent steps and data reverts the report back to almost source-level. Secondarily, though I've found a work-around to ensure the most recent Test CSV is included in the query folder, the 2 new test fields I created within the test CSV still are not populating even after performing [transform data] steps. Ugh, so frustrating.

[@AccessAnalytic]

Yeah retrofitting is always hard, I normally start to create a new query, get it to the same starting point as the old query then copy the code across in the advanced editor

[@ChrisSmithFW]

@@AccessAnalytic Not a bad idea and an approach I may ultimately take. Problem is that I have approximately 20 separate monthly data sources, at least 50% of which contain 50K or more rows of data, so updating each of those files with this parameter won't be an extremely fast process. Since I went through a similar exercise recently, focusing on back-end processes with very little improvement to the visuals during this time, I'll have to be more deliberate since clients typically want to see front-end improvements and sometimes don't understand the time and focus spent on back-end optimization. Thanks so much for the help here. Love your tutorials!

[@AccessAnalytic]

@@ChrisSmithFW No worries