How to Merge Excel Files with Different Headers in Power Query | List.Accumulate

[@LeilaGharani]

Stay ahead with our Weekly Newsletter. Get the latest insights and updates straight to your inbox 👉 link.xelplus.com/yt-c-newsletter

[@TheBigpapax30]

Ok. I was completely LOST. God Bless your advanced IT skill

[@sirrob0t]

By far one of the best (if not the best) excel content creator on youtube. Simply amazing Leila

[@LeilaGharani]

Wow, thank you!

[@PeteMackay]

OMG thank you! I I was struggling with this just last night and just abandoned my effort. I manually changed column headers in my data to get the append working. You are amazing, Excel is amazing and PQ is amazing! Your videos are doing a lot to foster people's love of excel so thank you for making them.

[@LeilaGharani]

Excellent! This makes me happy. Thank you.

[@chh8860]

1st Question - "Where did you learn your Excel skills?" Response - "From LGU" 2nd Question - Where? Response - "Leila Gharani University" 3rd Question - "Is it hard?" Response - "Yes ... very hard ... but the instructor is great"

[@shaunrowley7230]

This is brilliant I shall be considering the Power Query Course to purchase at the end of the month with using some of my salary payment - thanks again

[@cradleofrelaxation6473]

Ok! The most complicated formula I’ve ever seen in excel. I thought I was done with excel and DAX! Meanwhile this also depends on the fact that the column names not matching must be consistent and meaningful! Unlike the scattered column names my colleagues used to send to me. Well done Ma’am.

[@alanlamb6047]

Thank you for another great video I was curious to see if I could create a simpler approach for this specific problem. So, in the interests of sharing... Rather than the very useful but complex formula at 12:30 = Table.TransformColumnNames(TSalary_Table, each List.Accumulate(Table.ToRecords(Mapping) ... etc etc etc Suggested formula = Table.RenameColumns(TSalary_Table, List.Zip({Mapping[Before],Mapping[After]}), MissingField.Ignore) Translation The second parameter of Table.RenameColumns is a list of lists, for example {{"NOMBRE", "Name"}, {"DEPARTMENTO", "Department"}, {"SALARIO", "Salary"}} So, how to turn the mapping table into a list of lists Any of these options will do the job List.Zip({Mapping[Before],Mapping[After]}), or Table.ToRows(Table.SelectColumns(Mapping,{"Before", "After"})), or Table.ToRows(Mapping) Add the extra parameter MissingField.Ignore to ignore issues with attempting to rename columns that don't exist in specific files

[@enl3299]

Very elegant, thanks for sharing!

[@pointsofinterest4300]

I couldn't get the solution from the video to work for my use case, but your did. Thanks for sharing!

[@henrytaverner1803]

Excellento!

[@reng7777]

MY FRIEND YOU HAVEW SAVED MY LIFE!! HEADS UP TO YOU FOR YOUR FORMULA SUGGGESTED TO LEILA, THIS ONE DID WORK FOR ME.!! BEST REGARDS!!

[@joseangelmoreno5776]

Sir, thank you for taking the time to write this comment. You simply gave answer to my whole problem with renaming multiple columns at once, I'm delighted! Huge thank you.

[@kristenlloyd7774]

I’m desperately trying to teach myself excel in a night to get an amazing job- it’s a nightmare and you’re keeping me going right now. Thank you for your videos!!!

[@vijayarjunwadkar]

Hmmm, honestly this video is little overhead for me first time, so going to watch again, pausing and revising and I am sure I will get it! 😁But many thanks for this useful video, as always! 😊👍

[@LeilaGharani]

Hi Vijay. I fully understand. It took me a few weeks to understand how the list.accumulate function works 🤯

[@Chukwuzitremonyeka1735]

Leila, I love your honesty. Now I do not feel bad for not catching this explanation the first time. Thank you for all that you do for us and for Microsoft Excel, Leila.

[@gauravsukhadia638]

Concept of mapping is brilliant because people send all sorts of messed up data and to perform ETL operations this is game changer.

[@CristianPaiu-h2w]

This example is very well explained and quite insightful. I'd like to highlight one issue when utilizing the Text.Replace function. The outcomes might be unexpected, as it could inadvertently replace segments of text within a specific column. This complexity increases further when dealing with source files that aren't in the .xlsx format, such as .CSV files

[@alankc1937]

Excellent video, this lesson is quiet advanced because it has some functions not easy to explain at first besides List.Accumulate (each, underscore, the record function), but you covered perfectly all this topícs in your Power Query course. If somebody reads this comment, take Leila's course, it's worth and mind blowing.

[@LeilaGharani]

Thank you!

[@ankitlahase1836]

same case as show in this video...only one of my excel file have more than one sheet and .. when i try this it catch only one sheet data.what need to do to catch both sheet data

[@elainemattias9465]

Hi Leila, I love all the content from your channel and also how didactically your explain everything. :) I started to use Power Query recently and this issue of having different headers hunted me from the beginning. To solve that I adopted a different approach though, from the one you explained in this video. I load the files with different language only as connections, duplicate the columns, and rename them to the English terms. Afterwards, I am able to append all the sheets. I think that the 2 approaches work well. The choice for the right method is probably personnel and depends on how many columns, how many sheets, and user familiarity with Power Query. Elaine

[@reimievans2529]

I just append all and then merge and rename the columns. If a new column appears on refresh I can go edit the merge step. But I'll try this method soon because I'd rather learn it before I actually NEED it. Then I'll eagerly await the appropriate messy data opportunity.

[@kasthuric8023]

I'm happy that I found you ... thanks for sharing your knowledge... means a lot for me for doing my fundamental analysis...your doing really a great work ...

[@sumitrathod2814]

Hi Leila your vedio most useful to us....!!!! Keep updating more vedio of power query...!!!! Thank you so much for Dil se...... ❤️❤️❤️❤️❤️❤️

[@21121990jay]

I've been using power query quite a lot but didn't know about this transformation. You're Awesome Leila.🤘

[@LeilaGharani]

Awesome! Thank you!

[@goodnewskasparyaodzramedo9097]

This is a big challenge for me. Thanks for the tutorial. It's given me a good headstart at resolving this challenge of combining data of same sizes but different field labels

[@sanjeevsoni6793]

It works, But is going overhead.. Three Cheers to Leila, Well expalined, Many Thanks for sharing the knowledge

[@LeilaGharani]

You're welcome!

[@chandankumar-np9ez]

Your videos are very helpful, thank you. Please make a video explaining how to use this technique before expanding the table.

[@somasundarammohan6202]

Thanks a lot, you have saved me 8 hrs of work that i do on a quarterly basis.

[@LeilaGharani]

Glad it helped!

[@somasundarammohan6202]

@@LeilaGharani I tried and I found something wrong with it. My two sources have "POLICY" and "POLICY_NO" both mapped to "POLICY NO". Query reflects only data from "POLICY_NO" and column head is changed to "POLICY NO NO". Could you please help on this or suggest where I can learn from?

[@wayneedmondson1065]

Hi Leila. Nice to see a Power Query topic this week! Great challenge and solution. Thanks for sharing :)) Thumbs up!!

[@LeilaGharani]

Thanks for watching Wayne! It's a bit of a jump from my previous Excel for Beginners video 😉

[@cdyard1]

As usual, a great video and great explanations. It'll take me time, though, to fully understand. But, even if I'm also working with your Udemy courses, these videos are a great help.

[@ITTricksScroll]

What a tutorial!!! Your videos are very effective and efficient for the MS users!!

[@joshuahess2649]

Thank you for the video, it really helped me understand some of the tools available. I'm still not sure how to solve my specific situation though. If you, or anyone reading this, knows how, please let me know! -I receive multiple excel workbooks from different clients every day. -I want to combine them into one sheet in one workbook. -They are NOT formatted as excel tables. -All of them merge and center the first row above their column "headers", and add text, sometimes different text between the clients. -I do not need this first row at all, but it must be there when the client sends it. -I do not want to manually remove the top row from each workbook. -Some of the "headers" match, some are different but mean the same thing, and some are unique and need to be incorporated, with blank spaces for the worksheets that don't have data in that column. -Some worksheets have a merged and color-filled bottom row, sometimes with text, sometimes the bottom two rows are merged (individually, not with each other). -I do not need any of the information in these merged cells. -Two of the columns are "DATE" and "TIME", and I need the entire end product to be sorted in ascending order for both. -The sheet names are always inconsistent.

[@rewur615]

Danke!

[@LeilaGharani]

Danke auch!

[@willp5309]

I like watching this video at 2x speed. Easier to understand. 😆 great tip! Thank you for all your do!

[@GeorgeKlucsarits]

Great example. Reminded me to go back to the course content and re-watch now that I have a very similar use case. Thanks!

[@mdexcelpro8495]

I am very excited really your teaching skills are another level

[@LeilaGharani]

So nice of you

[@antoineferrand149]

Hi! I’ve found an more direct function which is list.zip() ! It needs only {reftoconversion_table[before], reftoconversion_table[after]} and the final argument for missing elements is also very nice! Thank you Leila 😉

[@LeilaGharani]

Thanks for sharing, Antoine!

[@ankitlahase1836]

same case as show in this video...only one of my excel file have more than one sheet and .. when i try this it catch only one sheet data .what need to do to catch both sheet data?

[@pk5134]

Hi Leila, your tutorials are amazing ! Thank you Could you please do a tutorial on the underscore operator and the "each" keyword? I find these, especially the underscore, very confusing as I can't figure out which one to use. Thanks

[@ebrusalic4342]

I wish I could give thousands of thumbs up. I love it and will use it a lot

[@kethanchordia]

Woah.. this is like magic.. thanks for the wonderful video on our daily battle with inconsistent data.. really helpful 🙏🏻🙏🏻🙏🏻🙏🏻🙏🏻

[@LeilaGharani]

My pleasure 😊

[@zaydarendse2812]

Lovely!! you've just taken a load of stress off my table :) thanks for sharing!!

[@LeilaGharani]

You are so welcome!

[@pracashraz007]

Awesome Leila, with this session, I have become a big fan of you 😘😘😘😘 Like this can you make an another session for matching data also please

[@badteestore]

Thank you for your videos! I have learned so much! Keep them coming!

[@davidk919]

Thanks

[@LeilaGharani]

Thank you!

[@shyamadasgupta9423]

Hi Leila, wonderful video. Can you tell me when to use List.transform and when to use list.accumulate and if one is superior to the other. Thank you.

[@mduan314]

This is very complicated formula to transfer the name format. I hope there is a better and simplier method to fix the name format difference. Thank you!

[@asylux1096]

I hope you can help :) If anyone can ---- it's you. :) I've watched all your videos now countless times. I've posted this question in stackable, but you said in your class that KZbin was your discord so here is my question: What I'm trying to create is a series that repeats every 8 rows, that spills down until the calculated value equals a referenced cell. // **L15** This is a **$** amount. **My Example is as follows.** I need the top starting value, which is in row F, to calculate three variables as it spills down each row. **Cell F17 first calculation** and equals the dollar created from the following variables. // B17= % such as 34% // L17= Dollar amount used (which is dynamic via a data validator) // D17= Number of people to multiply As each row drops in the series... // B17 becomes smaller, 34%,21%,13%,8%,5%,3%,2%,1% // D17 grows larger by ^2= 10,20, 40, 80,100... The idea is for the top number and each next variable to use the sum 8 series range: // `D17:D24` which equals 2,550 Yes, I have created a much simpler process to ascertain what the value SHOULD be by simply using the following formula: =SUM(D$17:D$24)*(B17*$L$17) However, why I had to change my approach is listed below. The series or an array formula series, that repeats every 8 rows, however, as the repeating pattern occurs in column B B17: 34%,21%,13%,8%,5%,3%,2%,1% The growth patterer compounds in // F17 = the amount of compounded Value // D17 = the ^2 amount used by an expositional of 2 // L17 = the increase of $ amount used each cycle. // L15 = Target Number for the series to stop** The reason for this elaborate formulation is to predict changes via several input variables to find the STOP CONDITION dependant on F column is // >= L15 To achieve the correct top-level calculation my function is `={B17*$L$17}*{sum(offset(D17,0,0,8))}` This adds all the future cells for an 8 range series then multiplies them, providing the correct calculation. THE PROBLEM IS ONCE it SPILLS, the offset calculates the numbers in the NEXT repeated series, making the only correct calculation - the first row. I tried `={B17*$L$17}*{sum(offset(D17,0,0,$8))}`` But I can't lock an offset height value. I did find where someone wrote about using a // row(F1)-1 but I'm not sure how to implement that. THE PROBLEM CONTINUES Once I solve this spill issue The STOP CONDITION may happen at several different rows depending on the referenced cells, that contain the target number for the multiple inputs and variables that adust growth. Meaning that once the Value in the initial Value column **F** hits the target of the referenced cell // L15 = dollar amount The pattern stops. I hope this is understandable. Thanks for the help if anyone out there sees this. Thank you.

[@garylillich]

This is the type question that you may want to share on a Facebook Excel group, if not solved already.

[@mattschoular8844]

There is some very useful information in this video. I will need to watch it again for sure. Thanks Leila

[@LeilaGharani]

Glad it was helpful!

[@TheGreyhound31]

You are simply awesome... You make such complicated things look so simple... Thanks a lot for your videos🙏

[@genechicago4806]

Wow, your mastery of Excel and Power Query is outstanding! When you said, "...I understand this is a little geeky...." My thought was, "hmm., this is more like nerdy. Did she ever spend any time playing with other kids? She should try to solve the Riemann Hypothesis! I'm sure she would be able to solve it." :)

[@LeilaGharani]

Haha, it took me a looong time to get the hang of this function. But it's just so useful once you understand it.

[@genechicago4806]

@@LeilaGharani Thank you for sharing that! My brain was spinning while trying to understand that function. It also made me wonder about my mental capacity to learn, and I was wishing for better weather to ride my motorcycle instead. It’s encouraging to hear that even you struggled with it. I will persevere and watch this video a few more times so that it sticks to my brain. I have no doubt about its usefulness. Thank you sharing your knowledge, and have a good day! :)

[@lesportautrement]

Excellent and strong ! One question : how activate the formula suggestion in PQ ??

[@sachinrv1]

Wonderful video Leila. It's difficult to digest. One really need to practice this functions many times before getting a dab hand on it. Thanks. Can you also do a video on excel 365 solution to this different header problem?

[@mariaalcala5159]

I learn a lot from you! Great trick to handle format issues

[@EricHartwigExcelConsulting]

This video is amazing, thank you so much for sharing Leila! I have taken your course on mastering power query but i must have missed the part about List.Accumulate so i will need to go back through the course again but what great idea!

[@LeilaGharani]

Glad you like it. It's included in the Pro section on XelPlus.

[@paser2]

Brilliant use of power query. Thank you!

[@johnborg5419]

Thanks Leila. I need to watch this video many times to absorb :)

[@vipulagarwal5155]

hell of a video.. blown my mind into pieces... damn I'm trying this one to grab a hand on... Thanks Leila.. Love u lots..

[@smithanimal]

Power Query is a new animal I've been diving into at work lately. This is fascinating. I am already amazed at what you can do with just excel but this really has me ready to dive into the course and learn Power Query. Is Power Pivot similar to this or is that a different process all together?

[@samtrupt]

Amazing video, need to ask you question about slightly different issues not sure how to compute

[@rajeevgosavi4514]

Very well explained. However, we would like to follow your tutorial step by step, as you have explained that may be easier if you can provide a link to download excel files under reference. Thanks.

[@magylopez2308]

Gracias por tus excelentes videos Leila!!

[@anttitiilikainen8543]

Thanks for inspiring videos. I was stuggling to get my head around similar kind of problem. After some trial and error found alternative option to reach similar outcome with merge function. On the "Transform Sample File", when the initial table is open, first transpose whole table. Now the original column headers are in Column1. Then the Column1 is merged with file that contains mapping information (columns OldName and NewName). After merging NewName is expanded to visible and moved to lefthand side to first column. Then Column1 is deleted, table is transposed back to original layout and headers promoted. Then just append all files based on the sample file :)

[@successcentre1]

Thanks - your approach is simpler nut Kudos to Leila on the List.Accumulate function approach

[@RajaMaja-zk3dz]

exactly what I'm looking for, thanks again Leila

[@rajat13777]

Love your videos. Simple to follow & real scenarios. So if I have a text/ csv file, downloaded from the web (with garbage data as well) and I want output in a certain format in excel, what would be better - Power Query or Macro? Thxs

[@alessandratorsello9511]

Dear Leila I love your video tutorials, Although, this time, I don't seem to understand the basis of your video. I understood that to create a mapping table I must first manually list the headers ("BEFORE" column) of all the tables I am working on and then create a second column ("AFTER") with the names I want to transform the "Before" Headers column into. How can I handle this procedure if my tables are many? Won't I waste too much time collecting all the headers? What did I miss/not understand about your instructions? Thank you if you may reply

[@danielvolgan]

Hi Leila, thank you for the very useful videos. I have a question. Is it possible to, instead of combining and transforming multiple files in a folder, transform first then combine the resulting tables? How to do it? Thanks.

[@ExcelWithChris]

Still not right there with my knowledge, but going to save and watch when needed. Thanks for another brilliant one.!!

[@LeilaGharani]

Thanks Chris. This one does take a while to digest!

[@MabenitoIII]

This is going to save me 5 millions months of arranging data files from our regions. Plus I need not to work my system generated report any longer. Just download into a folder and that's it.

[@tapio_m6861]

This is great! Thank you a ton, Leila!

[@vivekphadke16]

Excellent, will have to create dummy files practice and understand. Thanks a lot, Leila

[@denconstutube]

Way past my skill-set or need, but great information and interesting to watch a master at work.

[@garylillich]

Do not feel alone. Sometimes, we use brute force to get a similar task done.

[@Sierratana]

Fantastic!! Just what I needed! You are clearly a genius! :)

[@LeilaGharani]

Glad it helped!

[@akshaymane7697]

Hi.. Thank for your efforts in this. Could you also suggest easier way of combining data with different headers ?

[@Jocedu06]

This is awesomely explained, great one!

[@LeilaGharani]

Glad you liked it!

[@borisgrushin868]

This is impressive, but honestly I feel like a macro could accomplish this with less effort. Maybe it's a lazy way of thinking from my end, but it's not just powerquery that is used but some very advanced pq functions. Maybe I just need to throw myself into work that is best done in powerquery so it becomes more second nature to me and then I can iterate to something as advanced as this but this just seems so complex. Definitely impressive, but seems a bit of over engineering. Thank you for opening our eyes to the options out there though.

[@ICOD73]

This is very useful. Now add this challenge. The data can be different too. Like for example (Elbow, Elb, Elb., ELBOW, EBOW). Can we use list.accumulate to modify the data under a specific column as well?

[@LeilaGharani]

That's easier to do. You could use Merge with a transformation table to map. If you need to map multiple words in the same field, then List.Accumulate will do the job.

[@rabbitwang214]

It is a excellent and hard lesson!!! Would you mind share your example files for us to practice? Many thanks

[@SavageGothamChess]

Thank you so much Leila for the video. Well explained

[@LeilaGharani]

You're welcome 😊

[@rogercerda9878]

Excellent video! I loved it!!! But, I have a question: What if the data is coming as range (not as table) and is having 2 unwanted rows on top of every single file?

[@chantellevandalen5806]

I had the same problem. I was able to get it to work by adding a line before the Table.TransformColumnNames. This is my M script: let Source = Excel.Workbook(#"Parameter (3)", null, true), Navigation = Source{[Item = "Sheet1", Kind = "Sheet"]}[Data], #"Removed top rows" = Table.Skip(Navigation, 2), #"Promoted headers" = Table.PromoteHeaders(#"Removed top rows", [PromoteAllScalars = true]), Custom = #"Promoted headers", TransformColumnHeader = Table.TransformColumnNames(Custom, each List.Accumulate(Table.ToRecords(COLUMN_MAPPING), _ , (state, current)=> Text.Replace(state,current[BEFORE],current[AFTER]))) in TransformColumnHeader

[@shagunsahani9881]

@@chantellevandalen5806 Hey, I am facing a problem that whenever I start typing the formula as mentioned in the video. I'm not able to see the dropdown formulas to use. Can you help me out with the same?

[@Lyriks_]

Thank you professor Gharani 🤗

[@jeremynorbury]

This again highlights just how weak the PQ development environment is - it is more or less impossible to debug List.Accumulate operations. How did you do it Leila?

[@mamadoubah3686]

Great video! Thank you!

[@SyedMuzammilMahasanShahi]

Thank you so much for this wonderful video. It's gonna help us a lot.

[@ZakNazif]

Great presentation, as usual! How to append from several tables (not files) loaded in my workbook with different column headings and numbers? Very much appreciate your response. Good Luck!

[@Galileo2pi]

Pretty useful, as always excellent, thank you Leila

[@LeilaGharani]

Glad you think so!

[@nitigyahanda3516]

Hi, thank you very much for this video! I have a follow up question. What if the other files have different number of columns and values in different formats? For example in my case, "May- 2024 Sales" file contains all columns from previous months but also additional columns that were added to it and wouldn't be present in say "Apr-2024 Sales" file. Ideally I want all older files to display Null in this new column but I am not sure how to do it. I've been stuck on this problem for a while, I'll really appreciate it if you can help me out.

[@TylerStanczak]

Random question, but how do you make your videos so clean with visuals and transitions. Do you use premiere pro :)

[@LeilaGharani]

Thanks for noticing 😊 Mostly we use Camtasia with a bit of Premiere Pro and After Effects.

[@TylerStanczak]

@@LeilaGharani Thanks for letting me know. I’ll have to try and improve my skills with those awesome tools. :)

[@mr.gk5]

Could you elaborate more on buffering mapping table? What do you mean exactly? I'm confused

[@Zugey1983]

Me sirvio demasiado este video para integrar mis tablas, mil gracias!!

[@ce917]

It would help users to follow along if you place the exercise files somewhere to download.

[@garylillich]

Wonderful idea.

[@ManojKumar-sk3fw]

Nice lesson and very useful

[@nunofaia]

Amazing Explanation Leila , i tried to reproduce the above example and it worked :) with a slight hiccup between the Column1 and Column11 as my before is just a list of Columns with a number (Column1, Column2 etc) for some reason the Current and Sate in the text.replace function recognizes Column11 as Column1 and therefore applies the after of Column1 instead of Column11 , is there a way to force the Text.Replace to look for the exact string and not an approximate :) ? Much obliged once again for the Amazing Video

[@user-iz9yp8hb3z]

First again! Love the explanation!

[@LeilaGharani]

Yay! Thank you!

[@RohitSingh-nu7ek]

You can also transpose and lookup from your mapping table. Again transpose then make header.

[@garylillich]

Do you use lookup before or after the transformation.??

[@RohitSingh-nu7ek]

@@garylillich after first transpose

[@thaotv]

Thanks for very usefull solution. I have two querries with different headers loaded from website, are there any way to append its?

[@mirrrvelll5164]

It is a very useful thing but in a case when you have "almost perfect" environment of data, or very close to that. But reality is of course different, where you are dealing with a jungle of data, which gives hard time to transform properly.

[@schrevan]

Is it possible to manipulate data tables with headers and sub headers? For example let’s say you have a list of 10 items in column A. Then columns B and C are sub headers for units sold and $ sold, respectively, for Store #1, the header. Then you have columns D and E as units sold and $ sold (sub headers) for Store #2 (header), and repeat for as many stores as you have. Is it possible to use this type of data table

[@hanslowell1994]

I understand the source tables. Your video just jumps into the mapping table. Did you create a new table for that? Where did it come from? I though Power Query would generate the new table... I can't figure out the first step here. How am I getting it FROM the original tables to the "Mapped table"?

[@dagmarab727]

You are amazing! Thank you for all your videos. Taking the chance, do you know how I can import all tables from multiple pdf files? I have a multiple pdf files and each file contain different amount of tables...

[@sherryfox9688]

Would love for you to provide a copy of this workbook. I am still having issues making this one work. Thanks!

[@warrentso2548]

Thank you for sharing! I have followed every step but it still not working in my case, i am not sure if it is because my data file is not a table. There are too many files that i cannot set them to table format one by one.

[@Texmex85035]

Leila, would assume that this function would also work if I were to have same line items but in different cell ranges from multiple worksheets?

[@fabricebourgeat8491]

Thanks Leila - as always great explanations (but it will take me time to "digest" this one...). Now you mentioned one thing at the very end about "buffer your mapping to boost the performance". What do you mean? I have start to use PwrQ 2-3 months ago, importing various files, apply all types of mapping (not on headers as I did not know how to do it so far...) but I am facing some random performance issues. I am a bit lost between when we should load the data in the data model, or only do a connection, or just load the data... Can you explain why it seems that some times it is super fast and sometimes it takes ages? Would the "buffering" you suggest help? Or may be you have already done a tutorial on this topic... Thanks in advance!

[@fernandacastillo6133]

This was so insightful! Loving your videos! I'm new to PW and I've run into another problem with a new data set generated from a site I use to track orders. It generates an excel file where One row has multiple columns with the information I need and there's no specific distance between columns that repeat that information although the columns all have the same header. Any ideas on how could I merge all those repeated headers into one column for every item?

[@samerbabikir8946]

Great video many thanks Leila

[@LeilaGharani]

You’re most welcome 😊

[@henychm]

Thank you for the tutorial. I have a basic question though. How did you manage to get the source.name column in the final report? Anytime I combine multiple sheets via PQ, I have never had it as par of my final report and I would really like to know how to achieve that. Thank you!

[@arpwable]

At 4:37, you can see there's an auto-generated "Removed Other Columns1" step. Click on the settings cog for that step, tick the box for the source and it should remain in your data. Not sure why there's a difference in default behaviour here!

[@superlaffin39]

Hi Leila, What if you have a mapping sheet for header reference, the AFTER table is in column A and the BEFORE is in column B and C and D?