Showing posts with label YouTube. Show all posts
Showing posts with label YouTube. Show all posts

Thursday, April 23, 2020

Multiple Keywords Search with Power Query


If you wanted to search for some keyword(s) you could use Ctrl+F (and select Find All) to do this. If there was one value in the column entries, a lookup might work (like VLOOKUP) or using the merge feature in Power Query, but if you needed to lookup a word in a string of text in the cell, those lookup feature don't work that way. If you had many keywords or a list of them, this makes it even more challenging. Also if you wanted to give this to your stakeholders and provide some interactivity and hide the steps for them to do this without using VBA or show functions in your worksheet, think about using Power Query to do this.

Thursday, April 16, 2020

Select Records in One Table that Aren't in Another Table


This video will cover the Anti Join feature in Excel Power Query. The scenario it fits would be where you want to select all records from one table that doesn't exist in another table. So when we are trying to check whether an item does not exist in another table it's going to be an anti join. Another example is when we are trying to find something that hasn't happened. However in this video we'll cover something that doesn't exist rather that something that didn't happen. So check it out!

Tuesday, April 14, 2020

Create All Possible Combinations from Two or More Lists in Power Query


If you have two or more list and wanted to figure out all the possible combinations of the items of those list, it would be called a cross join, many to many join or a cartesian join. This can actually be done easily with Power Query. It shouldn't take too long if your source list are small. But if your list are bigger, you could potentially have output that could be a table of several million rows or records. If you know Excel, it does support 1M+ records in a single worksheet, but not over 2 million records. An example in this video will cover 4M+ records but the workaround is to separate the records into a million each and put all of it into a text file. See the video to learn how generate all possible combinations from two or more list.via IFTTT

Thursday, April 9, 2020

Keyword Search in Two Columns with Power Query


If you have user that want you make an easier keyword lookup for a table of records, you could tell them to just filter the table or put some lookup formula (like VLOOKUP) together for them. But if they just want some easy to use field where they type up something and click here to get the results, maybe another solution would be to use Power Query. But remember to tell them that this is a case sensitive keyword search!

Tuesday, April 7, 2020

Split Records into Columns or Rows with Power Query


Have you ever received a table of data that is packed into one cell and you needed to split the records into multiple columns or multiple rows. The records from the original table may have commas or line breaks as the delimiter and you think there should be some delimiter function or formula that takes care of this. In this video, I'll show how it can be done using Power Query. There are two examples with both showing how to split a multiline cell into new rows or columns.

For those that don't have Power Query, there is the option of using the text to columns function in Excel and that video is at https://www.youtube.com/watch?v=cOfaYdL55AE&t=65s

Thursday, April 2, 2020

Keyword Lookup with Power Query


If you have user that want you make an easier keyword lookup for a table of records, you could tell them to just filter the table or put some lookup formula (like VLOOKUP) together for them. But if they just want some easy to use field where they type up something and click here to get the results, maybe another solution would be to use Power Query. But remember to tell them that this is a case sensitive keyword search!

Thursday, March 26, 2020

Make it Easier to Share Power Query Relative File Location (Text or TXT)


If you share Excel files with Power Query queries, you notice that it can be "broken" if you the source data is referenced outside of the worksheet itself. This video will cover how to get around an simple query that references a text (.txt) file. There will be another video later because this is the simpler solution that involves adjusting the Power Query privacy setting to get around the Formula.Firewall error message.

Thursday, March 19, 2020

Simulate the Tabular Form Pivot Table Layout in Power Query


Sometimes our stakeholders want a table to looks like the Tabular format that comes from a Pivot Table but they don't want to see all that extra functionality from the Pivot Table. They want to see the Tabular format without repeating values in some of the columns so it looks "cleaner". We can actually simulate this using Power Query but it involves some M code (just a little bit).

Thursday, March 12, 2020

Create a List of Random Numbers with a Seed Value


Random number generation is fairly straightforward in Excel. You can use the RAND or RANDBETWEEN functions to create random numbers but if you wanted to create a pseudo random number from a seed value it would take some VBA or another add on. Not any more, because with Power Query, setting a seed value for repeatable set of random numbers is possible. See the video to learn how.

Tuesday, March 10, 2020

Add a Random Number in Power Query


If you need to create random numbers in excel it's easy to use the RAND() function for a few numbers, but if you want to create a large amount of random numbers that might be a challenge. So if you wanted half a million or more numbers, think about using Power Query to do this. Also if you have a list of names (500K or more) and you wanted to randomly choose 100, adding a random number to that list and choosing a hundred random names from that list can be done in Power Query. See the video to learn how.

Thursday, March 5, 2020

Make it Easier to Share Power Query Relative File Location (XLSX source)


If you share Excel files with Power Query queries, you notice that it can be "broken" if you the source data is referenced outside of the worksheet itself. This video will cover how to get around an simple query that references another Excel (.xlsx) file

Thursday, February 27, 2020

Use PowerQuery to Change the Order of Cell Values or Text


If you've tried to rearrange the order of text or data in cells within Excel, you're probably familiar with the LEFT, MID and RIGHT functions. The combination of these functions help you reverse text or first and last names. You can rearrange the data of almost any kind of content in your cells with these function. Incidentally there are formulas in the Power Query M code that can do the same thing and this video will cover two examples.

Thursday, February 20, 2020

Generate Random Numbers in Power Query


Want to create random numbers in Excel? If you're familiar with RAND and the RANDBETWEEN functions, did you know there was an equivalent in Power Query where you can create a list of random numbers in each row. There's no command icon to click in Power Query, so you'll have to go into the M language (but it's fairly easy).

Thursday, February 13, 2020

Power Query - Create a Table that References Itself After an Update


There may be a time when you want to make an addition (like add a column) to your output table from Power Query and have that update show up in the right place. It becomes a query that takes its output as its new input, appends the new data and overwrites the old result with the new one. This can be called (or at least the concepts are similar to) a self referencing query, circular reference, recursive table, self join or self referencing table. Though it sounds complicated, the steps to do this are quite simple.

Wednesday, January 29, 2020

Approximate Lookup with INDEX & MATCH Functions


If you wanted to do a lookup that wasn't an exact match, you're probably looking to do an approximate match lookup. There are different functions that can perform a closest match, partial match or even a fuzzy match. One of the function you can use is the INDEX & MATCH combination to find the closest value to a looked up value. This video will cover a simple example of looking up a salesperson sales quantity to figure out what kind of commision to be given.

Tuesday, January 28, 2020

Use the Excel Geography Data Type & Filled Map Feature


Learn how to use the Office 365 Geography Data Type feature along with the Filled Map charts.

If you've to the Office 365 subscription, you have access to a linked data type that goes online to pull back lots of interesting background information based on a geography value. For example if you want to know info about the population of India, just type in the country name, turn it into a geographic data type and Excel will go online and pull info like population or area size auto-magically. And you can do this for a list of countries if you're trying to do some analysis.

To add more to this Excel goodness, you can chart this data on a map with the feature called Filled Maps. It basically is a choropleth map that uses color or shade to help visualize the geographic data. Filled Maps is also an Office 365 enhancement, but it is looking to be available in Excel 2019.

Wednesday, January 22, 2020

Find Records that Don't Have a Match in Both Tables


This video covers the ANTI JOIN. This occurs in SQL when two tables returns rows from the first table where no matches are found in the second table. It's consider the opposite of a semi-join when in two tables the result returns rows from the first table where one or more matches are found in the second table. In this example an anti-join is finding records where there's no match between both tables. However in Excel Power Query, there is not anti join option when using the merge feature. However the workaround in to use the left and right anti join features and then append the results. Confused? Well check out the video to see how it could be done.

Wednesday, January 15, 2020

Create a Full Outer Join in Excel Power Query


If you wanted to merge two tables together to figure out what records may have "missing', blank or null values in the fields, a full join or full outer join may be the solution for you. The use for this type of join can be for reconciliation or trying to discover what type of values you don't have from one table to another. If you want to see all records from table, look into using the full outer join merge capability in Excel Power Query.

Wednesday, January 8, 2020

Lookup Two Tables to Bring Back Matches in Both


This video will cover the INNER JOIN merge capability in Excel Power Query. This type of join returns records from two reference tables that have matching values in both tables. We are basically looking for matching records from two tables and only those records that have a common field ( or key) are brought back in a results table. In INNER JOIN comes from SQL and it's among the different join types available. It's nice that it comes with Power Query so you don't need a fancy database like Oracle, SQL Server or MySQL.

Tuesday, January 7, 2020

Use the MAXIFS and MINIFS Functions


If you've got Excel 2016, you've got the MINIFS and MAXIFS functions. They let you find the minimum (lowest) and maximum (highest) values based on one or more conditions (criteria). The criteria can be numeric or text and this video will show you how.