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.

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

Power Query can append tables from a folder effortlessly when those tables are the same (i.e., same number of columns and same column header names). But if the columns have different field names or the order of the columns are not in order, the append process will not work well. However there are methods to make it work and this video will cover one of them.

Power Query Append Worksheets in Same Workbook

If you wanted to do a search for keywords in the text of a column or multiple columns you could use the find all feature in Excel. But if this was something where you wanted to bring back the records for those columns that had the keyword and report it back to a stakeholder, you'd want something more complete. Enter Power Query; it can provide the mechanism for you to do keyword search in multiple columns and systematically bring back those rows or records and also take into account the case (i.e., case sensitive words) of the keywords.