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

Рет қаралды 106,649

Күн бұрын

Download Files:
Start File: excelisfun.net/files/EMT1357S...
excelisfun.net/files/1357Sour...
Finish File: excelisfun.net/files/EMT1357F...
Download File: people.highline.edu/mgirvin/ex...
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

Пікірлер: 186

@oviwan42 2 жыл бұрын

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

@excelisfun 2 жыл бұрын

It is amazing what Power Query can do!

@evgeniam8882 4 жыл бұрын

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 4 жыл бұрын

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 3 жыл бұрын

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

@ExcelInstructor 3 жыл бұрын

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

@pravinshingadia7337 4 жыл бұрын

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 4 жыл бұрын

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

@frazicl 3 жыл бұрын

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!

@tosaksettharungson 4 жыл бұрын

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!

@conobabino2471 7 жыл бұрын

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

@excelisfun 5 жыл бұрын

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

@stevemorales 7 жыл бұрын

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

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

@BillSzysz1 7 жыл бұрын

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 7 жыл бұрын

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

@IdrisShenaee 7 жыл бұрын

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

@SamiElzaim 4 жыл бұрын

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

@retamapark 5 жыл бұрын

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

@haihathanh5061 4 ай бұрын

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

@cecilrivera4241 6 жыл бұрын

Totally amazing. Great job, very insightful and helpful.

@dannyaguirre2309 7 жыл бұрын

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 7 жыл бұрын

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

@himanshudalai1028 6 жыл бұрын

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

@timmytesla9655 2 жыл бұрын

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

@qiyixin6399 7 жыл бұрын

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

@excelisfun 7 жыл бұрын

You are welcome!

@danh2016 4 жыл бұрын

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 4 жыл бұрын

You are welcome, Dan H!!!

@dcesano1 3 жыл бұрын

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

@excelisfun 3 жыл бұрын

Glad it helps!

@stephenmakin5263 7 жыл бұрын

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

@tunaikinyanguk5493 Жыл бұрын

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

@CraigHatmakerBXL 7 жыл бұрын

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 7 жыл бұрын

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

@mattschoular8844 5 жыл бұрын

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

@excelisfun 5 жыл бұрын

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

@MrVinayBN 4 жыл бұрын

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

@excelisfun 4 жыл бұрын

Glad the videos help!

@nishantkumar9570 6 жыл бұрын

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

@DougHExcel 7 жыл бұрын

Great example of using power query to do multiple unpivots!

@excelisfun 7 жыл бұрын

Thanks, Doug !!! : )

@sequa74 Жыл бұрын

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

@user-xo3yu6pg4n 7 жыл бұрын

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

@excelisfun 7 жыл бұрын

You are welcome!

@Muuip 5 жыл бұрын

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

@excelisfun 5 жыл бұрын

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

@rococoanalytics-byasif2287 7 жыл бұрын

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

@excelisfun 7 жыл бұрын

You are welcome!

@camiloag47 4 жыл бұрын

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 4 жыл бұрын

You are welcome, Juan!

@jacksonhz 7 жыл бұрын

you´re the best! always outstanding explanations!

@excelisfun 7 жыл бұрын

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

@bitechmacrobitechmacro5066 7 жыл бұрын

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

@excelisfun 7 жыл бұрын

You are welcome so much! : )

@pmsocho 7 жыл бұрын

Awesome video!

@excelisfun 7 жыл бұрын

Thank you, Awesome Teammate! : )

@ekaterinachurilova8299 6 жыл бұрын

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.

@iankr 2 жыл бұрын

Great stuff. Many thanks.

@arunprasad72 5 жыл бұрын

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

@excelisfun 5 жыл бұрын

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

@giuliolaudani1153 5 жыл бұрын

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

@mueez89 3 жыл бұрын

One of the best videos ever!

@excelisfun 3 жыл бұрын

Glad you like it!!!

@davidferrick 2 жыл бұрын

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

@moyaonne55 7 жыл бұрын

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

@excelisfun 7 жыл бұрын

You are welcome!

@johandryreyes 7 жыл бұрын

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

@excelisfun 7 жыл бұрын

You are welcome!

@alinmiklos 7 жыл бұрын

Awesome. Happy New Year !

@excelisfun 7 жыл бұрын

Happy New Year to you too : )

@epinedoh 3 жыл бұрын

Perfect! Thanks

@FRANKWHITE1996 4 жыл бұрын

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

@chuckhamdan1283 4 жыл бұрын

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

@johnborg6005 4 жыл бұрын

Great staff. Practise, Practice, Practice.

@excelisfun 4 жыл бұрын

Yes, yes, yes!!!!!

@rockrick820 7 жыл бұрын

Great video! Thank you so much!

@excelisfun 7 жыл бұрын

You are welcome!

@vananhnguyen6717 Жыл бұрын

Incredible! I wish I know this sooner

@excelisfun Жыл бұрын

Glad it helps now, Van!!! : )

@EricGiroux 7 жыл бұрын

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 7 жыл бұрын

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

@ContentedSoul 7 жыл бұрын

A brilliant solution to an all too common work problem

@djkhmerchild 7 жыл бұрын

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

@excelisfun 7 жыл бұрын

You are welcome!

@maneshzaveri6277 7 жыл бұрын

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

@excelisfun 7 жыл бұрын

You are welcome!

@tableaupro3233 5 жыл бұрын

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.

@wmfexcel 7 жыл бұрын

Simply awesome! :)

@excelisfun 7 жыл бұрын

Glad you like it! : )

@AhmedAbdalalim 3 жыл бұрын

Very helpful Many thanks

@excelisfun 3 жыл бұрын

You are welcome!

@Telecomm2004 7 жыл бұрын

awesome one :) thanks for sharing

@excelisfun 7 жыл бұрын

You are welcome!

@fakarullahyusop3992 3 жыл бұрын

Thank you....really helpful

@excelisfun 3 жыл бұрын

Glad it helps!

@markhenderson3771 2 жыл бұрын

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

@lrhammar 7 жыл бұрын

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 7 жыл бұрын

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

@rrrprogram8667 7 жыл бұрын

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

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

@oliverbird8320 7 жыл бұрын

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

@DragonsTaco 7 жыл бұрын

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

@excelisfun 7 жыл бұрын

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

@sebfromgermany3819 3 жыл бұрын

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?

@argokusumandani9773 3 жыл бұрын

Thank you sir

@excelisfun 3 жыл бұрын

Welcome!!!!

@zaighamuddinfarooqui1705 7 жыл бұрын

Excellent.

@excelisfun 7 жыл бұрын

EXCELlent comment!! ; )

7 жыл бұрын

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 7 жыл бұрын

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

@andresfrancojunor 7 жыл бұрын

Genius, thanks

@excelisfun 7 жыл бұрын

You are welcome!

@RS-el7iu 6 жыл бұрын

youre a great tuuuuuuuutooooooooooor REAAAAALLLLLYYYYYYYYYYYYYY... and thank you so much :)

@excelisfun 6 жыл бұрын

You are welcome so much, raffi!!! Thanks for the support with your comment, Thumbs Up and Sub : )

@kamalnaserkroor4834 4 жыл бұрын

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,

@FRANKWHITE1996 4 жыл бұрын

Its a kind of magic 👍

@excelisfun 4 жыл бұрын

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

@annelauricecastro214 7 жыл бұрын

amazing

@kamranb1369 6 жыл бұрын

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 6 жыл бұрын

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?

@luisfco.martymatos5431 6 жыл бұрын

This is magic! :^)

@excelisfun 6 жыл бұрын

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

@gaid81 7 жыл бұрын

Fantastic

@excelisfun 7 жыл бұрын

Glad you like it!

@cuba_rj 2 жыл бұрын

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

@jamesmurdock7958 7 жыл бұрын

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 7 жыл бұрын

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

@velinraivr 4 жыл бұрын

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

@nishantkumar9570 6 жыл бұрын

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

@msmith3090 6 жыл бұрын

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

@Sonwinlim 4 жыл бұрын

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!

@vhc6600 2 жыл бұрын

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?

@williamarthur4801 2 жыл бұрын

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.

@luisfco.martymatos5431 6 жыл бұрын

Could this be done without customs functions?

@reagannyadimo8742 4 жыл бұрын

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?

@zxyzxyzyzyugfd 5 жыл бұрын

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

@excelisfun 5 жыл бұрын

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.

@sushmagupta9976 6 жыл бұрын

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 6 жыл бұрын

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

@AbhayGadiya 7 жыл бұрын

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 7 жыл бұрын

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.

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

@meditubebrand 7 ай бұрын

Repeat Clean & Transform Steps for Many Excel Files

@MasterofPlay7 2 жыл бұрын

is this still relevant with excel 2019?

@Tagenarine1987 6 жыл бұрын

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 6 жыл бұрын

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: kzfaq.info/get/bejne/qLmBqr1iyZnGYZs.html Thanks for the support with your comment, Thumbs Up and Sub : )

@Tagenarine1987 6 жыл бұрын

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

@mattnyman9933 5 жыл бұрын

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

@excelisfun 5 жыл бұрын

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

@excelisfun 5 жыл бұрын

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

@mattnyman9933 5 жыл бұрын

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

@excelisfun 5 жыл бұрын

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

@Telecomm2004 7 жыл бұрын

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 7 жыл бұрын

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

@AGmAGicman 6 жыл бұрын

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 4 жыл бұрын

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

@reagannyadimo8742 4 жыл бұрын

@@salahaddin2009 has it worked?

@kevzgu 4 жыл бұрын

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

@muntean9010 3 жыл бұрын

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 3 жыл бұрын

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

@chuckhamdan1283 4 жыл бұрын

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 4 жыл бұрын

Experiencing similar challenge too

@vananhnguyen6717 Жыл бұрын

I have same problem. How did you solve that?

@joffrimpong7720 3 жыл бұрын

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 2 жыл бұрын

Same

@mathew9665 7 жыл бұрын

Hi thank you for your great videos, I'm new to power query/get & transform. I've already been able to do a number of time saving takes thanks to your videos. Could I ask a question I trying to find a method to remove invalid UK postcodes from my data set, I have the rules from this link en.wikipedia.org/wiki/Postcodes_in_the_United_Kingdom#Validation but I don't know how to start (being new, and unfamiliar with M programming)