Search This Blog

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

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.