Extract a Cell Value for Column Values

If you wanted to get or extract a cell value and use it for input into values for a table column or category, it's an easy copy and paste. However if this is something you do on a recurring basis or have lots of worksheets to do this with, it would be a prime candidate for some automation. VBA would be one way of doing this but if you're a VBA novice like me, it'll take some time. Enter Power Query. Also there's a way in the step where you can reference or call a previous step in part of the overall steps to do this. With a few clicks and less than 5 minutes in set up, you can have an automation on what was a manual task before.

📝 This description may contain affiliate links and we'll receive a small commission if a purchased is made using the links (but at no additional cost to you). It'll support the channel and so more videos like this can be made. Thanks for your support!

#excel
#msexcel
#doughexcel

-~-~~-~~~-~~-~-
Please watch: "Convert Table in a PDF File to Excel"
-~-~~-~~~-~~-~-

[contextures]

Great Power Query tips, Doug! It's smart to rename those steps, especially if you're referring to them later.

[thomasguckholz]

Wow ! That's such a great trick ! Didn't know it was possible to refer to previous step this way ! Thanks so much !

[wayneedmondson]

Hi Doug. Nice one! Worth noting is that you can get there directly by typing a little code into the Add Custom Column formula box. From your video time signature 2:15 (working off the Changed Type step), add a custom column and type the formula: = #"Changed Type"{0}[Column2]. The {0} is the row positional index operator (base 0) being applied to Column2 of the the Changed Type step. So, it pulls the value from Column2, row1 (i.e. California) and puts it in all the rows of the new Custom Column named Custom (unless you give it another name). From there, you can delete the top 2 rows, promote the headers, etc. Just another way to get there vs. the UI which requires more steps. Nothing wrong with either method. I'm trying to learn more M code and so like to find ways to bypass or shorten the number of steps created by using just the UI. Fun stuff!! Thanks for all the useful lessons and videos. Learning a lot from you :)) Thumbs up!!

[realpulsecoin]

Great! I learn alot by seeing how you work. Never thought each step as like individual steps. Thanks!

[nobilismaximus]

You missed out merging all the files into o e file with the state record injected. That way it’s better suited to bulk loads etc