Search This Blog

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

Thursday, May 28, 2020

Changing Time Zones in Power Query


When you get date and time values from another systems, sometimes it is in a time zone that is different from yours. For example you might get a field that has a date/time/timzone in UTC or GMT format, but you need to doanalysiss in Western time. How do you convert the timezone? You could actually do the calculations and offset it by adding or subtracting hours to the UTC value. Switching time zones gets more complicated if you have to deal with daylight saving time. You'd have to find out which zonehour had daylight saving applied and which did not. How you handled the time zones begins by setting or configuring an initial time zone in Power Query for the date/time value.

Tuesday, May 26, 2020

Unpivot Data with Null or Error Values in Power Query


If your familiar with Pivot Tables, it's basically taking a proper table and summarizing the data for further analysis. However what if you got a pivoted table (aka crosstab or contingency table) already and wanted to do the reverse? In Power Query, there's the option to unpivot (uncrosstab or sort of a transpose) a pivot table view. However it does certain things to the data if it has errors or null values and maybe you don't want Power Query to remove the empty cells or show the error cells. This video will cover both scenarios and the steps to address them.

Thursday, May 21, 2020

Add Multiple Series of Data to X Y Scatter Chart


If you're creating a scatter plot it may begin with one series of x,y data. But later on you may want to put a second set of data or a second line into your chart. You may have 2 variables, 3 variables or multiple variables! Basically this are multiple groups or multiple sets of data and you'd want to plot two or more things at once. This vid will show you how to quickly add multiple scatter plots into your graph chart and even cover labeling your series of data. Well it covers more of how to label your series of data via the legend and even though Excel can give you two or more colors to separate out those series, you may want to use markers to help differentiate it more.

Thursday, May 14, 2020

Extract Hyperlinks from Sentences in a Column


If you were to pull out hyperlinks (or URLs) from a table, it would be fairly easy if it were in the beginning or end of the contents in a cell by using some combinations of the LEFT, MID, SEARCH or FIND functions. But if the URL in the text cells were all over the place and part of a paragraph in a cell, it might be more challenging or could involved using VBA. Common cases would be to pull out URLs with http, https or www and this video will show how to extract those type of URLs using Power Query.

Thursday, May 7, 2020

Calculate Median in PivotTable


Pivot Tables are one of the greatest analytical tools in Excel for it's capability to slice and dice data. It can summarize data by sum, average and even standard deviation. But the one of the basic statistical measures is the median and unfortunately that is missing from the pivot table options. Fortunately you can include that option in and it just involves creating the pivot table and adding it into the data model to write a simple DAX measure. See how it works by watching this short video.

Thursday, April 30, 2020

Customize Pivot Table Default Layout Style


When you create a PivotTable it defaults to certain views and if you create Pivot Tables often you probably adjust it a lot. Maybe you don't like the compact Pivot layout with it's nested view and prefer Tabular with repeated row values. Once the Pivot table is created you can manually change it by going into Options and changing it for each feature which this video will cover. That works if you don't create Pivot Tables that much, but if you do create them often and don't want to always click and select everytime just make those views the default. When you go into the Backstage view in Excel, just adjust the default settings and they will apply every time you create a Pivot and become your new Pivot Table defaults. Check out the video to learn how to customize this now.

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.