Excel Magic Trick 1357: Power Query Function to Repeat Clean & Transform Steps for Many Excel Files

[@MegaKarthik17]

Just brilliant.. solved a huge business problem using this as a backloading mechanism for power apps. Tysm

[@excelisfun]

Glad this could help!!! How are you using Power App?

[@MegaKarthik17]

Have a power automate setup to upload the clean data on Sharepoint list and then using a Canvas app for front end

[@excelisfun]

@@MegaKarthik17 Cool!

[@oviwan42]

ubelievable. This Video is from 2016 and i feel now like coming from the stone age. Exactly what i need from now!!

[@excelisfun]

It is amazing what Power Query can do!

[@evgeniam8882]

What a magnificent video! This was EXACTLY what I was looking for these days! Every subject, every problem, every challenge or question someone might have, will be solved and found in this channel masterfully explained by the Excel Guru of all times!! Thank you so so much!!!!! :)

[@excelisfun]

Yes, with over 3000 videos, there are a lot of topics at excelisfun : ) I am just glad that you can find what you want, learn, have fun and solve your problem, Evgenia!!!

[@ExcelInstructor]

@@excelisfun Shame that there is no option for custom functions to be kept like macros. like in PersonalCustomqueryfunctins.xlsb :D that would rock!

[@ExcelInstructor]

@@excelisfun Would it be possible somewhere around here 15:32 to load each table to separe sheet using PQ? (everything b4 15:32 would stay as it is. just the loading option.

[@danh2016]

This was a great video. I just used it to solve a real business problem I had where expanding columns didn't quite cut it. Thank you.

[@excelisfun]

You are welcome, Dan H!!!

[@dapperlink]

Had to watch this 3 different times with some Power Query hands on in between, but 3rd time was the charm and I could anticipate your next move all the way through. That wasn’t you, it was me. This is excellent! Thank you!

[@pravinshingadia7337]

Brilliant video Mike - keep coming back and referring to them! I managed to built a dashboard at work using all the knowledge I have learnt from you the past few months so thank you.

[@excelisfun]

I love hearing that the videos hekp you!!!! Great work on building the dashboard : ) Thanks for your support each time you come back with your comments and thumbs ups : )

[@dannyaguirre2309]

This is by far one of the best methods to consolidate sheets quickly using Power Query, I love it ! Now I see also the option to load the Data Table to my Power BI Data Catalog or load to my data gallery. Thanks Mr. Excel !!

[@excelisfun]

You are welcome! -- excelisfun, Mr Excel is a different guy : )

[@conobabino2471]

ExcelIsFun is goldmine!! I plan to watch every episode with practice exercise. Thank you!

[@excelisfun]

You are welcome, cono - thanks for your Support and Sub : )

[@CraigHatmakerBXL]

First: Absolutely superb video. Just a note to viewers - When we MUST use crosstab tables scattered across worksheets for input (I see this often), Mike shows us how to transform them into data tables suitable for PivotTables and other reporting and analysis functions. Where we CAN avoid crosstab tables, we can avoid these steps too.

[@excelisfun]

Glad you like it! Yes, I 100% agree. if you are serious, Proper Data Set all the time. In all of my courses and playlist, that is always day 1 teaching. As you suggest, it makes our lives much easier : )

[@dcesano1]

I have been lookikg for this function for the last 2 months.. Can't thank you enough!

[@excelisfun]

Glad it helps!

[@cuba_rj]

Hi, i wonder if you have any updated version of this given the recent changes to PQ since 2016. Maybe less steps are needed?

[@tosaksettharungson]

Excellent example of creating and using function + transforming multiple files in a single video. This goes to my favourite list for sure. Thanks so much!

[@timmytesla9655]

Brilliant. I have always searched wondered how this could be done. Thank you!

[@markhenderson3771]

Thanks. Duplicating and editing to create the function at 06:35

[@sequa74]

Excellent video, great learning to apply in day today office works, thanks a lot Mike.

[@tunaikinyanguk5493]

This is precisely what I have been looking for. Thanks, Mike

[@DougHExcel]

Great example of using power query to do multiple unpivots!

[@excelisfun]

Thanks, Doug !!! : )

[@velinraivr]

Awesome! is there a way to do it without coding?

[@negosyok]

thank you for the detailed explanation in this video. just want to ask how to add date column based on file creation date? because the source content does not have date column in it.

[@chuckhamdan1283]

I finally made it to work by inserting the missing transactions referring to my earlier message and it worked.

[@BillSzysz1]

This is the best introduction to the functions of PQ i've ever seen. This video shows us that "the devil is not so black as he is painted". :-))

[@excelisfun]

Thank you for the kind words, and for the PQ poetry!!! : )

[@IdrisShenaee]

Dear ExcellsFun I am new to excel I have a data to analyse it but I don't really no how to do it and where to start. Would you be able to help me please would be much appreciated, here the link of the data drive.google.com/file/d/0B5P6KT7My9fIVmMxTUNKSFlMbE0/view?usp=sharing Many thanks

[@vananhnguyen6717]

Incredible! I wish I know this sooner

[@excelisfun]

Glad it helps now, Van!!! : )

[@johnborg6005]

Great staff. Practise, Practice, Practice.

[@excelisfun]

Yes, yes, yes!!!!!

[@Sonwinlim]

This is great but I need one step further and I hope you can help. I need to create a custom column that refers to a cell value. I did it but the issue is for the rest of the files, the column refers to the cell value of the first file instead of taking the value of the cell in their own (aka the Date). I think I will have to create a dynamic source but I can't figure this out. Please help!

[@mattschoular8844]

Power Query is truly powerful. Another great video. Thinks Mike

[@excelisfun]

Glad it is great for you, Matt!!! Thanks for your awesome support : )

[@MasterofPlay7]

is this still relevant with excel 2019?

[@haihathanh5061]

Great! It's all what I need. Thank you ExcellsFun!

[@vhc6600]

Hi Mike, is there a way to do this but not to combine the multiple files but process them and output them as separate files still?

[@qiyixin6399]

Always have some new tricks found here, thanks so much for your work.

[@excelisfun]

You are welcome!

[@mueez89]

One of the best videos ever!

[@excelisfun]

Glad you like it!!!

[@arunprasad72]

Thanks so much, this is an excellent video showing how to use PQ to consolidate data from multiple files with multiple headers.

[@excelisfun]

You are welcome, arunprasad n!!! Thanks for the support with your comment, thumbs Up and Sub : )

[@Muuip]

Role model for online tutorials.Very useful, many thanks!

[@excelisfun]

You are welcome! I am happy that the videos help you! Thank you, muuip, for the support with your comment, Thumbs Up and Sub : )

[@camiloag47]

Thanks a lot for this introducción to functions of PQ, I saw a lot of videos in spanish (I am from Colombia), but nobody tackles this topic, so I had to search in Inglish and your explanation is very clear and complete, excellent videos, again thanks a lot.

[@excelisfun]

You are welcome, Juan!

[@davidferrick]

Good to know a nice solution for a situation I hope I never run into. :)

[@stevemorales]

Your videos are incredibly helpful and detailed. Thank you for taking the time and doing this

[@АсетЖайлаубай]

Thank you!!! You are best master of excel.

[@excelisfun]

You are welcome!

[@ContentedSoul]

A brilliant solution to an all too common work problem

[@SamiElzaim]

Once again your one of your tutorials saved my day! Thanks a million for this Mike!

[@pmsocho]

Awesome video!

[@excelisfun]

Thank you, Awesome Teammate! : )

[@retamapark]

Well done! I appreciate the steps listed as text in the Excel tab. Nice.

[@williamarthur4801]

Great and easy to follow only I couldn't get it to work , only one file returned an unpivoted table, the rest came up Expression.Error: The column 'Yanaki' of the table wasn't found. I've still learned a lot regarding basic principles and always enjoy working through your videos.

[@rrrprogram8667]

Why doesn't you tube allow to hit like button multiple times ... As usual ...Thanks for everything you are sharing ..

[@stephenmakin5263]

Un-pivot multiple columns! That is an amazing tip, thank you

[@cecilrivera4241]

Totally amazing. Great job, very insightful and helpful.

[@lrhammar]

Holy Moly. I've done something similar with VBA macros but this workflow is much faster. I'm kinda excited to try this out with data pulled out of Revit software.

[@excelisfun]

Love to hear it!!! Thanks for the Thumbs Up and Sub!

[@MrVinayBN]

You are just incredible! I always learn a lot from your videos. Keep up your good work. Kudos to you ✌🏻😃

[@excelisfun]

Glad the videos help!

[@jamesmurdock7958]

I learn more from your videos than anywhere else. But even though I have the most recent download of 365 Pro, I don't have "Create Custom Function" in the menu bar. Any advice?

[@excelisfun]

Do you mean "Invoke Custom Power Query Function" button? Anyway, it maybe that you need to get the latest update.

[@maneshzaveri6277]

Super useful video for me....now need to implement it....Thanks

[@excelisfun]

You are welcome!

[@kamalnaserkroor4834]

Hello Mr. Girvin, Videos are very comprehensive. However, in this video if the product list is not at same as the main sheet product list, then, it will through an error:" An error occurred in the ‘’ query. Expression.Error: The column 'Sunspot' of the table wasn't found. Details: Sunspot)." Where 'Sunspot' is a product in main sheet. Best,

[@reagannyadimo8742]

If you use any of the files and apply the function on the folder, then we get an error. Does it mean all products must be available?

[@rococoanalytics-byasif2287]

Great learning and first time I learned creating function in Power Query...thanks @ExcelIsFun

[@excelisfun]

You are welcome!

[@learning_with_irving4266]

Why do you need to use a separate function instead of just letting the initial query to do the applied steps for all files?

[@moyaonne55]

Thank you for your sharing knowledge. I help me a lots on excel work that I have to support my boss.

[@excelisfun]

You are welcome!

[@giuliolaudani1153]

@ExcellsFun; thanks for the amazing video! It is possible to use the some functionality "add column" + Excel.workbook(Content) to open a set of mail saved into a folder?

[@himanshudalai1028]

Thank you Mike for this excellent video. Great Learning as always.

[@oliverbird8320]

Hi, Dr E have you seen pop up window said " Initialization of the data source failed. Check the database sever ..." ?

[@AhmedAbdalalim]

Very helpful Many thanks

[@excelisfun]

You are welcome!

[@luisfco.martymatos5431]

Could this be done without customs functions?

[@ekaterinachurilova8299]

Brilliant explanation! Thank you so much. I have a trouble to apply function if I don't need to transpose and unpivot. In my files that had to be consolidated there is 1 raw on top with column names. But I need to consolidate only table from the sheet without other data on it. And I cannot manage to import it. I import only the whole sheet. It seems I don't modify correctly query to function.

[@DragonsTaco]

Good Video, thank you. What did you use to create the video of your screen? The automatic zooming is very effective. Thanks!

[@excelisfun]

That is not automatic zooming, that is all hands on editing done in Camtasia Studio. : )

[@kamranb1369]

Hi Mike, Great tip, Thanks for that.I am getting one issue though it only works if products are same in all the tables if there is something different it doesn't run the custom function.Is there a way to make this process dynamic?

[@excelisfun]

I thought in this video that the example I showed had products that WERE different. Maybe you could send a small sample of the problem you are having, both how data looks at the start and what it should look like at the end to excelisfun at gmail?

[@zxyzxyzyzyugfd]

Could we also create the query from New Query -> Create from Folder (where all the excel files are ) without using a function ?

[@excelisfun]

Not that I know of. We need to build the function the first time so we have all the steps that we can save and use for all the files.

[@nishantkumar9570]

Thank you very much! It's an awesome and very useful technique.

[@jacksonhz]

you´re the best! always outstanding explanations!

[@excelisfun]

Thank you for the kind words!! Thanks for the Sub, comment and Thumbs Up : )

[@FRANKWHITE1996]

I like this video a lot! I mean A LOT!!!!

Nice videos man. I have learnt a lot with them. One question about this one: Couldn't you just have created the unpivot steps right in PQ without creating/calling the function? In that case will it work when adding additional files to the folder?

[@excelisfun]

I am not sure how to repeat M Code without making a function. That is not to say that it cannot be done, though...

[@fakarullahyusop3992]

Thank you....really helpful

[@excelisfun]

Glad it helps!

[@epinedoh]

Perfect! Thanks

[@alinmiklos]

Awesome. Happy New Year !

[@excelisfun]

Happy New Year to you too : )

[@nishantkumar9570]

I have a question, is there any way to link the folder path with a cell value to make it dynamic? Thank You

[@msmith3090]

Ken Puls has covered this at his blog. You'll have to read through carefully and refer to the linked blogs posts. It works. - www.excelguru.ca/blog/2015/03/25/prompt-for-a-folder-at-refresh/ ~Mt

[@sebfromgermany3819]

Heya, I love your channel, kept me occupied while I was in COVID quarantine last month. Fortunately COVID is over, but my fascination for PQ continues. Your videos of merging Excel files or Excel worksheets are a great inspiration. There is one detail I could not figure out. I would like to have a column referencing the source e.g. workbook name or file name. Does anybody have an idea how to do this smartly?

[@FRANKWHITE1996]

Its a kind of magic 👍

[@excelisfun]

It is!!!! I think that is why we can have so much fun, FRANKWHITE1996 : )

[@meditubebrand]

Repeat Clean & Transform Steps for Many Excel Files

[@tableaupro3233]

Mike, I like the way you start with a picture to show the End. Beautiful. I found an error when I applied the function on the files in Start Folder. Given the good intro I got in MSPTDA09, I was able to find the error and fix it. It is happening because the text "Date" is appearing as the first cell in the files in the start Folder, where in the start Excel where you have pasted the data to start transformation steps, that first cell is empty. Though it took a bit of time to figure out the issue, it was a great learning experience - maybe, you should fix it so that others could avoid getting the error I got.

[@mattnyman9933]

is FX at the beginning of the name of the function due to naming convention or does Power Query look for FX?

[@excelisfun]

I just named it that for me. Power Query does not require it.

[@excelisfun]

Hopefully the videos are helpful! Thanks for your support with your comments, Thumbs Up and Sub : )

[@mattnyman9933]

@@excelisfun Been your fan for years. recommend you to my friends all the time.

[@excelisfun]

@@mattnyman9933 Thanks for the support - it helps me to keep posting : )

[@rockrick820]

Great video! Thank you so much!

[@excelisfun]

You are welcome!

[@EricGiroux]

Hi Mike, Thanks you so much for these tutorials, I"m actually reading M is for (DATA) Monkey, your PowerQuery (Get & Transform) video series is a wonderful complement. By the way, today I finally received your updated C-S-E book, I now have both! ;-)

[@excelisfun]

You are welcome! Yes, Data Monkey is a great book! Thanks for buying both of my books and supporting the cause : )

[@djkhmerchild]

Awesome tutorial..... Thanks for the post.

[@excelisfun]

You are welcome!

[@argokusumandani9773]

Thank you sir

[@excelisfun]

Welcome!!!!

[@muntean9010]

this video just gave me a a "hard reset" on what i thought i knew...So you did this only so you catch the name of the sheet in the table? otherwise you could have just done the same PQ steps for the whole folder at once having the same result(if the name of the sheet is the same at least)

[@excelisfun]

It is fine to do it with a function or with user interface steps. It is all amazing : )

[@zaighamuddinfarooqui1705]

Excellent.

[@excelisfun]

EXCELlent comment!! ; )

[@johandryreyes]

WooooW that's Amazing. Thank you!!!!

[@excelisfun]

You are welcome!

[@sushmagupta9976]

Hi - the piece that I'm still confused is that how do I use this over and over again? I close excel, shutdown for the night. I come in tomorrow. I receive new files to shape and transform. Do i have to rewrite the steps?

[@excelisfun]

No, but you would have to copy and paste the M Code.

[@joffrimpong7720]

Please after invoking Custom Power Query function I received an error msg of" An error occurred in the ‘’ query. Expression.Error: The column 'Column1' of the table wasn't found. Details: Column1 " could you help you help me out?

[@shaddwatson2673]

Same

[@andresfrancojunor]

Genius, thanks

[@excelisfun]

You are welcome!

[@bitechmacrobitechmacro5066]

Spectacular :O Magical Video. Thank you So Much!!!!

[@excelisfun]

You are welcome so much! : )

[@a14266]

amazing.... but zip folder is not available for download.

[@excelisfun]

Oh no... I am not at work right now to post it, I will have to do it soon. I will comment here as soon as I get it posted... Sorry about forgetting to post the zipped folder. : (

[@excelisfun]

I just posted the zipped folder. : ) Sorry for the delay.

[@a14266]

Thanks for reply.. ... Thanks for all your effort to educate us.

[@excelisfun]

You are welcome!

[@Tagenarine1987]

I recently started using power query in excel 2013 and the expand button works on excel files so I guess something changed from the time this video was uploaded.

[@excelisfun]

I wouldn't use the expand button on Excel files because there are too many potential objects in Excel files, whereas with Text Files each file has just one object. I just posted a video about this here: kzbin.info/www/bejne/qIS0qI1nmZiVZ8k Thanks for the support with your comment, Thumbs Up and Sub : )

[@Tagenarine1987]

Just watched the video and now I get what you meant by the different objects in the excel files. I will be using the Excel.Workbook function from now on :)

[@chuckhamdan1283]

When using the downloaded files and executing step by step following the video I get an error saying the following: An error occurred in the "query.expression.error the column 'Crested Beault of the Table was not found. The same for Yanaki and then the same for sunset and sunshine. How to correct this? I would appreciate your feedback. Sincerely

[@reagannyadimo8742]

Experiencing similar challenge too

[@vananhnguyen6717]

I have same problem. How did you solve that?

[@AbhayGadiya]

This function will work if I have one of the file open and it is not saved in the same folder. If I create a new blank file, I would need to amend some steps in this function a little bit for source data. Else I will have to copy the steps from advanced editor into the blank file created and then invoke that function.

[@excelisfun]

I am not sure I understand what you have said. But the purpose of the Consolidating Query was to get all the similarly cross tabulated tables in various Excel Files files that are in a specific folder.

[@Telecomm2004]

awesome one :) thanks for sharing

[@excelisfun]

You are welcome!

[@wmfexcel]

Simply awesome! :)

[@excelisfun]

Glad you like it! : )

[@gaid81]

Fantastic

[@excelisfun]

Glad you like it!

[@Telecomm2004]

I have an excel sheet, I need to attach PDF documents to it in the certain cell. no preview is required. then I need to convert excel sheet(which contains PDF attachment) into one PDF file. So, when I open it I will see (excel sheet converted to pdf) and the attachment (in pdf) as a one pdf document Can I have VBA code for that? Thanks in advance.

[@excelisfun]

I am not good with VBA. Try posting question here: mrexcel.com/forum

[@annelauricecastro214]

amazing

[@yolindaburns3447]

I am not sure why but this did not work for me. I got this error "1 arguments were passed to a function which expects 2" any feedback as to what I did wrong?

[@excelisfun]

What function? What does your M Code look like?

[@yolindaburns3447]

@@excelisfun Thanks for your reply!! I tried to replicate the function in your video to transform 6 identical excel files so I could append them into 1. This is the code: (Fx_Transformation_HC_WD)=> let #"Removed Top Rows" = Table.Skip(Fx_Transformation_HC_WD), #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Location Address - Country", type text}, {"Market", type text}, {"Cost Center - ID", Int64.Type}, {"Cost Center - Name", type text}, {"Company", type text}, {"Worker", type text}, {"Legacy ID", Int64.Type}, {"Associate ID", Int64.Type}, {"Job Title", type text}, {"Job Code", Int64.Type}, {"Time Type", type text}, {"CF - LRV Business Area Band (Text) [From Worker]", type text}, {"Pay Rate Type", type any}, {"Exempt", type any}, {"Original Hire Date", type datetime}, {"Hire Date", type datetime}, {"Date of Birth", type any}, {"Effective Date for Current Position", type datetime}, {"Job Family", type text}, {"Gender", type any}, {"Manager Name", type text}, {"Manager ID", Int64.Type}, {"CF INT5023 Manager Title LRV", type text}, {"Supervisory Organization", type text}, {"Race", type any}, {"Scheduled Weekly Hours", Int64.Type}, {"Job Grade", type text}, {"Compensation Grade", type any}, {"Annual Salary", Int64.Type}, {"Hourly Rate", Int64.Type}, {"Veteran Status", type any}, {"Protected Veteran", type any}, {"Self-Identification of Disability Status", type any}, {"EEO-1 Category", type any}, {"Location", type text}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Location Address - Country", "Market", "Legacy ID", "Time Type", "CF - LRV Business Area Band (Text) [From Worker]"}), #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Original Hire Date", type date}, {"Hire Date", type date}, {"Effective Date for Current Position", type date}}), #"Removed Columns1" = Table.RemoveColumns(#"Changed Type1",{"Manager Name", "Manager ID", "CF INT5023 Manager Title LRV", "Supervisory Organization", "Scheduled Weekly Hours", "Location"}), #"Filtered Rows" = Table.SelectRows(#"Removed Columns1", each ([Company] = "US0066 Sysco New Mexico (Division of USA I)") and ([Worker] "Purged Person")), #"Changed Type2" = Table.TransformColumnTypes(#"Filtered Rows",{{"Date of Birth", type date}}), #"Replaced Value" = Table.ReplaceValue(#"Changed Type2",null,"No",Replacer.ReplaceValue,{"Exempt"}), #"Removed Columns2" = Table.RemoveColumns(#"Replaced Value",{"Job Family"}), #"Changed Type3" = Table.TransformColumnTypes(#"Removed Columns2",{{"Annual Salary", Currency.Type}, {"Hourly Rate", Currency.Type}}), #"Reordered Columns" = Table.ReorderColumns(#"Changed Type3",{"Associate ID", "Worker", "Cost Center - ID", "Cost Center - Name", "Company", "Job Code", "Job Title", "Pay Rate Type", "Exempt", "Original Hire Date", "Hire Date", "Date of Birth", "Effective Date for Current Position", "Gender", "Race", "Job Grade", "Compensation Grade", "Annual Salary", "Hourly Rate", "Veteran Status", "Protected Veteran", "Self-Identification of Disability Status", "EEO-1 Category"}) in #"Reordered Columns" I created the function just like you did on the video but unfortunately it does not work.

[@luisfco.martymatos5431]

This is magic! :^)

[@excelisfun]

Glad you like the magic! Thanks for the support with your comment, Thumbs Up and Sub : )

[@AGmAGicman]

I'm getting this error when I invoke the custom function An error occurred in the ‘’ query. Expression.Error: The column 'Yanaki' of the table wasn't found. Details: Yanaki

[@salahaddin2009]

I have the same, I re did the excercise 3 times now.

[@reagannyadimo8742]

@@salahaddin2009 has it worked?

[@kevzgu]

In the function query, remove all the change types steps. Then the Error will go away

[@seasinealbarran807]

Hey! How are you? I have 11 employees.. 10 nml week is M-F, 1 is S-Thurs. The 10 that works M-F must rotate Sat and Sun based on seniority factoring in days unavailable days per employee to include approved leave, etc. All 11 employee also rotation federal holidays base on seniority (date of hire). I want the worksheet to be automated if an unavailable date changes , or a new employee comes on board for example. Where do I start? I know nothing about excel. If you cant help me. where can I fine a reliable professional excel person to make to for me? Thank you!!

[@excelisfun]

Try this great Excel question site: mrexcel.com/forum

[@SuperAshraf1972]

HI Mike, could you please help me to create a pivot table from a folder that has multiple excel files with huge data more than a million rows in each excel file

[@SuperAshraf1972]

my email ( [e-mail] )