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

Download Files:

Goal: Unpivot and Clean Cross Tabulated Tables in multiple Excel Workbook Files and Create a Single Proper Data Set. Create a Power Query Function to repeatedly perform the Clean & Transform Task across many Excel Files. Lean how to use the new Invoke Custom Function option for a Custom Column. Learn many powerful features in Power Query (Get & Transform):
1. (00:16) Introduction: Overview of whole process, including looking at the files that we need to import, clean, transform and consolidate
2. (02:00) Build Custom Power Query Function with the steps listed below (3 – 12)
3. (03:47) Transpose Table (to deal with the fact that there are two column headers with conditions that need to be Unpivoted)
4. (04:30) Promote Headers
5. (04:48) UnPivot Other Columns based on the two columns Date and Fair
6. (05:40) Rename Columns
7. (06:05) Close and Load to “Create Connection Only”
8. (06:34) Duplicate Query
9. (06:52) Look at M Code and how it is set up and automatically written when you use the User Interface in Power Query
10. (08:27) Add lines of M Code to convert duplicated query to a Custom Power Query Function
11. (09:42) Import Files From Folder: Import Multiple Excel Files with Cross Tabulated Tables that need to be cleaned and Unpivoted.
12. (10:34) Transform extension column to lowercase letters and then Filter for only Excel Files with extension “.xlsx”
13. (11:25) Add Custom Column with Excel.Workbook Function to get Excel Objects, such as Sheets.
14. (12:58) Expand Custom Column to show objects, and to expose sheets with the Cross Tabulated Tables.
15. (13:37) Filter to import only Sheet Objects.
16. (14:09) Filter Out Sheet Tabs that contain the word “Sheet” (Sheet Tabs that do not have a Sales Rep Name.
17. (14:30) Remove Other Columns (Not Data or Name)
18. (14:42) Invoke Custom Power Query Function
19. (14:34) Remove Data Column
20. (14:39) Expand Columns
21. (15:55) Set Data Types and rename columns
22. (16:34) Load to Excel Worksheet (Table, Only Create Connection or Data Model)
23. (17:36) Test Updating by dropping new files in our folder
24. (18:20) Summary

[kamalnaserkroor]

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,

[evgeniam]

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

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

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

[MasterofPlay]

is this still relevant with excel 2019?

[pravinshingadia]

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.

[MegaKarthik]

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

[velinraivr]

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

[oviwan]

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

[BillSzysz]

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

[cecilrivera]

Totally amazing. Great job, very insightful and helpful.

[conobabino]

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

[stevemorales]

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

[williamarthur]

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.

[haihathanh]

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

[meditubebrand]

Repeat Clean & Transform Steps for Many Excel Files

[danh]

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.

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

[markhenderson]

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

[vhc]

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?