Monday, March 29, 2021

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.

Monday, March 22, 2021

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.

Monday, March 15, 2021

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.

Monday, March 8, 2021

Keyword Search in Multiple Columns with Power Query

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.

Monday, March 1, 2021

Excel Lookup to Return the Last Value

Usually when you're doing lookups in Excel, it brings back the first value that matches. But what if you want the last value and did not want to sort the table to do that? You can actually perform this type of lookup quite easily in Office 365 version of Excel with the XLOOKUP function. But what if you don't have O365? Well you're not stuck, cause you can combine some functions (INDEX, LARGE, IF and ROW) to do the same thing. I'll show examples of both and offer a bonus on how to create a drop down list near the end.

Monday, February 22, 2021

Use the Power Query Data Type Customization

In the Microsoft Office subscription or Microsoft 365 version of Excel there's data types that let you get a lot more information from just one value in a cell. There is a stock data type that lets you view the latest information of that public company like the stock price or shares outstanding; and that is just based on the name of the company in one cell. Now you can actually create you own custom data type courtesy of Power Query. Think of it as a small table or list that you can expand from just one cell in your worksheet. You can have any time of structured table of related information and it all gets encapsulations in one column of multiple row cells. See the video to learn how this can be done.

Wednesday, February 17, 2021

Create a Clickable Image Map in SharePoint


In SharePoint classic pages, you were able to have a clickable image map similar to an ordinary web page. However with SharePoint modern pages, Microsoft seemed to have taken that ability away. There is actually a workaround for this using another popular Microsoft product - PowerPoint. You can create the hotspots with multiple links for each part of an image, upload this using the file viewer web part and bam(!) you have a pseudo image map along with the ability to mouseover and see the link without writing html.