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

[@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?

[@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 😀

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

[@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 😀

[@rosa3709]

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

[@AccessAnalytic]

Cheers ☺️

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

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

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

[@ivicanuck]

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

[@AccessAnalytic]

Glad to help.

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

[@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 😀

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

[@tedmoy]

Always loses me at the codes part in these videos

[@AccessAnalytic]

Yeah the M code isn't the most friendly. I found it just takes time and exposure to slowly sink in

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

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

[@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 ⭐

[@txreal2]

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

[@AccessAnalytic]

Great

[@anasschehima5164]

Thanks a lot , that's really brilliant !

[@AccessAnalytic]

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

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

[@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/

[@pauliboo2]

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

[@IvanCortinas_ES]

Sparkly. One line of code, many problems solved.

[@fcasalen]

anyone tried this with a dataset that has a lot of columns? Mine has 120 columns (less than 100k lines). After tweaking as stated in the video, the expand step became extremely slow. After 2 hour it didn't finshed, while without the tweak it takes less than 5 seconds.

[@AccessAnalytic]

Wow.. good to know that it’s a limitation. I’ve never had to deal with that many columns

[@garethhill7926]

Hi, what did you change to get it to run quickly again? (the tweak you avoided using) I'm having this issue. (about 68 columns but only about 1200 lines across about 40 files) It runs through it eventually, but takes way to long for such a small amount of data. Thanks for any help and thanks to OP

[@homeboy8656]

Hey Do you get any solution for that because I am also facing the same problem? Plz reply

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

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

[@lashakhorava6918]

I can't see Measurement6 from file C

[@AccessAnalytic]

Are you trying this out on your own computer Lasha? I don't know why Measurement6 wouldn't show

[@lashakhorava6918]

@@AccessAnalytic Ok, thankyou anyway its a very useful video

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

[@gini77oo]

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

[@AccessAnalytic]

You’re welcome

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

[@juliusbiliran]

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

[@AccessAnalytic]

Glad to help

[@suhaimiabdazais]

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

[@AccessAnalytic]

You're welcome

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

[@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 😊

[@zac3697]

I am getting the below error when enter the formula = List.Union( List.Transform(PreExpand[Transform file],each Table.ColumnNames(_))) Error: Expression.Error: The column 'Transform file' of the table wasn't found. Details: Transform file Can you please help me?

[@AccessAnalytic]

Transform file is the name of the helper query not a column reference

[@felipesignorellireis7839]

Very Good. Cheers from Brazil

[@AccessAnalytic]

Thanks Felipe

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

[@MasTommy21]

Hi, something weird happened to my table. I combined 2023 Jan-Dec csv file (268 columns) and 2024 Jan-Mar csv file (50 columns) But now somehow i got many blank columns _1, _2, ..., _194, etc in the 2024 csv file. Is that because they have different column numbers? any idea how to deal with it? I check the transform file, = (Parameter12) => let Source = Csv.Document(Parameter12,[Delimiter=",", Columns=268, Encoding=1252, QuoteStyle=QuoteStyle.None]), #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]) in #"Promoted Headers"

[@AccessAnalytic]

When files are appended the number of columns is then consistent for all imported data, the columns that don’t exist in a particular source file will contain nulls

[@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 )

[@FRANKWHITE1996]

Thumb up 👍

[@sands7779]

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

[@AccessAnalytic]

Thanks

[@joeswright86]

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

[@AccessAnalytic]

Great!

[@shubhabratadey]

Beautiful

[@AccessAnalytic]

Cheers

[@rahulmg23]

Hello Wyn, thank you for the solution. one issue i am facing here is, after executing the query Table expand with "Headings" . it went to the process/loading. i think it wen to the loop, no result. The formula i am using is = Table.ExpandTableColumn(ReadyToExpand, "Transform File (4)", List.Union(List.Transform(PreExpand[#"Transform File (4)"],each Table.ColumnNames(_)))) . all the excel files are in sharepoint folder. please let me know where is the issue. thank you.

[@AccessAnalytic]

I'm not sure sorry. Maybe post to techcommunity.microsoft.com/t5/microsoft-excel/ct-p/Excel_Cat or community.fabric.microsoft.com/t5/Power-Query/bd-p/power-bi-services with some sample files / screenshots

[@lenzypetty9371]

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

[@AccessAnalytic]

You’re welcome

[@vacilando86]

İt's easy but literally life saver

[@AccessAnalytic]

Great to know, thanks Alpay

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

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

[@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!😄

[@syedaneesdurez9880]

Amazing trick ..

[@AccessAnalytic]

Thanks

[@malchicken]

Much appreciated ^^

[@AccessAnalytic]

No worries Hendrick

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

[@alimoudden444]

Im a data analyst junior and i need your help for combine some table please can you give me your email

[@AccessAnalytic]

Hi for help I recommend posting here: Excel techcommunity.microsoft.com/t5/microsoft-excel/ct-p/Excel_Cat Power BI (Power Query): community.powerbi.com/t5/Power-Query/bd-p/power-bi-services

[@shamamajamz7103]

Thank you so much!

[@AccessAnalytic]

You’re welcome

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

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

[@cesarsaldana3429]

amazing!!!!

[@AccessAnalytic]

Cheers

[@jeongwonc6462]

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

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

[@vasilisa6128]

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

[@AccessAnalytic]

Thanks great to know

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

[@Dev_Bartwal]

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

[@AccessAnalytic]

😀 Thanks for taking the time to leave a kind comment

[@Wastrelification]

Insane man thanks

[@AccessAnalytic]

No worries

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

[@loserfireable]

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

[@AccessAnalytic]

Glad to help Gabby.

[@shirleymoreman6725]

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

[@AccessAnalytic]

No worries Shirley

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

[@syedahsan1226]

Why it is taking so much time for me. I have like 30,000 rows and 50columnes of data. It is taking more than 10 mins.

[@AccessAnalytic]

Try amending Headings by wrapping in List.Buffer( your current step code )

[@JoseGuerrero-pj1fk]

I have a problem. I did the query in Power Query-Get data from folder but with only one. Now, I include the data file for August 2022 and in the step before expanding, if I click on the table, the table is saying that it is empty.

[@AccessAnalytic]

Without screenshotts and digging into things it's impossible for me to help sorry. I'd recommend posting your question and screenshots here techcommunity.microsoft.com/t5/microsoft-excel/ct-p/Excel_Cat

[@cheikhhakim5864]

thank you sir

[@AccessAnalytic]

You’re welcome

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

[@kw6725]

Unfortunately, List.Transform does not work. I double checked and triple checked the syntax. It just comes up as an error.

[@AccessAnalytic]

Hi KW, post a screenshot of your Power Query screen to aka.ms/excelcommunity and someone will be able to spot what's going wrong

[@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 🙏🏼

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

[@zzota]

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

[@AccessAnalytic]

You’re welcome

[@davidcaple5547]

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.

[@AccessAnalytic]

Not sure on that sorry

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

[@deeepika91]

THANK YOU!!

[@AccessAnalytic]

You’re welcome

[@SharathAcharyaputtur]

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

[@AccessAnalytic]

You’re welcome

[@mihnearazvan1]

If I try to do the same thing loading from a sharepoint folder, it does not automatically detect my header columns, so my columns are Colum1, Column2 etc...Any idea how I can set it to promote the headers?

[@AccessAnalytic]

Use the transform sample file query to promote the headers

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

[@tonynewnham5894]

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

[@AccessAnalytic]

You're welcome Tony 😁

[@emanueledelzoppo5164]

Thanks for you amazing videos. Does it work with .csv files? Cause it's not working for me and this is the only difference.

[@AccessAnalytic]

Ah yeah, there's an extra step for CSVs, go to the Transform sample file Source step and delete the reference to the number of columns, then the additional steps per the video should work

[@s1ngularityxd64]

I've tried it also in the transform sample file, to clean (fill up) columns - bad idea my mashup container now eat up my Ram and everything becomes super slow😨

[@AccessAnalytic]

Number of columns, types of transforms and data sources all impact it

[@norbertschmidt6532]

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

[@AccessAnalytic]

Glad to pass on the tips Norbert

[@SoNonWoo]

What if it's a SharePoint folder and you're filtering to the latest file and the latest file has an extra column? Your video pointed me in the right direction, but it was a slightly different problem with a different solution. So I thought I would share the solution here. Go to the Transform Sample File. Increase the number of columns

[@AccessAnalytic]

Thanks Kevin

[@SoNonWoo]

@@AccessAnalytic Thank you for all your great content and help

[@gloryanncagande5504]

This helps me a lot but when I'm done saving my file, I found a lot of duplicates. Is there a certain codes to put in to avoid getting a lot of duplicates?

[@AccessAnalytic]

You can add a remove duplicates step at the end maybe ( highlight all columns and right click and choose remove duplicates

[@baskis69]

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

[@AccessAnalytic]

Cheers Arnau

[@datascienceworld3854]

Thank you

[@AccessAnalytic]

No worries

[@m.i.drissboutriha7665]

brilliant

[@AccessAnalytic]

Thank you

[@sohailansari9337]

Awesome video..👏 Do we have a function to combine tables please assist 🙏

[@AccessAnalytic]

Table.Combine ?

[@z.719]

Brilliant. Just in time. Thanks for sharing.

[@AccessAnalytic]

Thanks for commenting 😁

[@khulumanimlotshwa9329]

I followed this by the rule but my data posted NULL values, someone kindly help

[@AccessAnalytic]

I would suggest posting some screenshots and ideally some sample files here techcommunity.microsoft.com/t5/microsoft-excel/ct-p/Excel_Cat Hopefully someone can spot the issue

[@cjjr85]

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

[@AccessAnalytic]

Excellent

[@jumpropemonk7703]

Has anyone tried this formulae for different problem where inconsistent data is coming from sheets rather than separate workbook?

[@AccessAnalytic]

Similar sort of idea, but at the start when in the navigation window Right Click on the file name at the top (looks like a folder icon) and choose transform. Filter for the applicable sheets, and add a column that promotes the sheet headings to table headings. let Source = Excel.Workbook(File.Contents(YOURPATH\YOURfILE.xlsx"), null,true ), #"Promoted Headers of each sheet" = Table.AddColumn(Source, "PromotedHeaders", each Table.PromoteHeaders([Data])), StartingPoint = Table.SelectColumns(#"Promoted Headers of each sheet",{"PromotedHeaders"}), ColumnNames = List.Union( List.Transform( StartingPoint[PromotedHeaders], each Table.ColumnNames(_) )), ReturnToStartingPoint = StartingPoint, #"Expanded PromotedHeaders" = Table.ExpandTableColumn(ReturnToStartingPoint, "PromotedHeaders",ColumnNames) in #"Expanded PromotedHeaders"

[@yahoobolt]

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

[@AccessAnalytic]

Awesome 😊

[@ExcelUnlocked]

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

[@AccessAnalytic]

Happy to help!

[@lucianoelias8961]

AWESOME!

[@AccessAnalytic]

Cheers

[@mayur3651]

I had a doubt. If we add a new column in any of the tables, will it get added in the consolidated table ???

[@AccessAnalytic]

Yes it will

[@zuhairalmutawa9941]

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

[@AccessAnalytic]

Glad to help Zuhair

[@CelsonFC]

Learning from Brazil... Thank you!

[@AccessAnalytic]

You're welcome