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. With a few clicks and less than 5 minutes in set up, you can have an automation on what was a manual task before.

Combine Worksheets & Keep Worksheet Names in Column in Power Query

Combining small tables in worksheets that have the same column structure and names is a simple task. But if you had to combine or append the tables from a workbook that had a LOT of worksheets (or tabs), that would be a mind numbing exercise. Also how would you know what worksheet name it came from? With the small worksheet example you could create a helper column and manually enter the worksheet name for those respective table. If there's 10 or more worksheets (30? 40?), that'll be painful and you might think about learning VBA to do this. Fear not because with a few clicks in Power Query, it can be done in less than 3 minutes.

Use Descriptive Statistics Functions in Excel

Curious about statistics like mean, median or standard deviation? Want a feature that gives you some basic descriptive statistics in a couple clicks? This video will cover a basic overview of how to get those type of descriptive statistics with the Data Analysis command and the different functions you can use to get to the same for each individual statistic.

Power Query Append Queries Not Working - Mismatch Columns Order & Name

Combining or appending files should be a straightforward process and using Power Query makes it easier. If the files that have the same header names and are in the same column order it’s really easy. However if the header names of different between files and the column orders are also different (i.e., mismatched column names and mismatched column orders) it’s a bit harder. If you only have a few files or tables to combine or append it’s just a copy and paste. But if you do this on a recurring basis or if there’s a LOT of files, it’s much harder. But still Power Query will make it easier, so check out the video to see how.

Find Largest or Smallest Value based on Criteria

If you wanted to find out the biggest or smallest values in a table there’s a couple ways that can be done. Also if you wanted to find the next largest or smallest value, there’ also a function for that. AND...if you had multiple criteria to find big or small values there's recently added functions (Excel 2019 or Office 365) that let you do that. See how all this can be done in the video and there's also a bonus tip on how to create dropdown list.

Power Query Append Queries Not Working - Mismatch Column Names

Appending tables in Power Query is fairly straightforward. The great thing about using Power Query is that the files can all be in one folder and with the magic of the append feature in power query it quick and easy. That is unless the tables in the files are different like having mismatch column names. Lets assume that the column structure is the same; the tables in each file have the same number of columns and its just one sheet per workbook and even that is the same (i.e., Sheet1). However the column names in each workbook is slightly different. For example column one in one file is called "ID" and column one in the second file is called "IDs". The one additional character can make appending the files produce the wrong result. You can edit each file to make sure the column names are the same and that is not too much trouble if it is just a few files. But if it is more than ten files then it becomes a chore, especially if this is something that is done on a recurring basis. There is a workaround, so check out the video to learn how.

Use Power Query to Change Text in Cells or Columns

Change text in Power Query is easy to do. Some transformations include change the case (i.e., upper vs. lower), adding or removing spaces and changing special characters. All this can be done with in the Ribbon tools to the text in the cells. However if you wanted to change all the headers cells to make the text change case or remove characters, that would involved going into the M code. It's not hard, but it's worth it if you have a lot of column headers to change.