Search This Blog

Showing posts with label IFTTT. Show all posts
Showing posts with label IFTTT. Show all posts

Wednesday, July 15, 2020

Chord Diagram in Power BI


A Chord Diagram or Network Radial Diagrams as a type of chart that is popular in data viz programs. You can't find it in Excel and not natively in Power BI. However it is a data visual that you can import from the Power BI marketplace (and it's from Microsoft). It's a visually appealing chart and quite popular to data viz practitioners. You might think that making this is hard, but with the chord data visualization import from the marketplace, it's really easy. And you don't need fancy programs like R or Python.

Monday, July 13, 2020

Animate a Bubble Chart in Power BI


Animated Bubble Chart probably got their start from Hans Rosling's TED talk demo using their Gapminder tool. This data visualization has come a long way with quite a few tools that enable this animation. Power BI is one of those tools that have this capability. It is part of the scatter chart visualization using the play field. However there is also another data visualization tool you can download from the Power BI Visuals marketplace called Play Axis that gives you some more options. This video will cover creating an animated bubble chart using both.

Wednesday, July 8, 2020

Excel Combination Chart of Sales vs Target


If you need to chart some kinds of data to a goal like sales to a target, Excel makes this easy with the combo chart of a column chart along with a line chart (initially). The columns can be the sales and the line series will represent the target. Most times this is enough when you have a target that doesn't change much. However if there are change to the target having the line vary may not looks visually appealing. Another example in this videos that is also a combo chart is actually using the combination of two column charts but have them on different y axis.

Monday, July 6, 2020

Convert Numbers to Words in Excel


Convert numbers to words seems lot a often sought out request for Excel. One day it may be a function or even command that can be entered or clicked on the ribbon. However it's not, but there is a workaround solution that Microsoft has provided in the form of VBA. They actually posted a knowledge article (along with the code) on a function call SPELLNUMBER. Just do a web search for this text to find the Microsoft page to get the code and instructions on how to apply it. One caveat is that there will be some errors to troubleshoot through, but it wasn't so bad.

Wednesday, July 1, 2020

Risk Assessment Chart in Excel


In Excel, creating a risk assessment chart (aka impact / probability chart) is fairly easy. All you need is the risk assessment matrix (with a not so large scale) and about fifteen minutes of time. Once finished you can put this into your favorite presentation software (PowerPoint?) and even make this into a template for the future.

Thursday, June 25, 2020

Case Insensitive Lookup with Power Query


If you are trying to do a case insensitive keyword lookup for a table of records, you could just use the filtering capabilities in the Table feature. But if you want to make it more easy for users, maybe having them enter a word in a cell and than refreshing a table would be better. One solution would be to use Power Query!

Thursday, June 18, 2020

Use Power Query to Append Multiple Tables


Appending or combining tables that have common headers in not hard to to if you only have a few. But if you have a couple or combine common tables on a recurring basis, it would be nice to automate this. Power query give you this option to make it easy to do. Whether you're joining one to three tables or if you do this on a monthly, weekly or daily basis, this can be done quickly once you have it set up.

Thursday, June 4, 2020

Dynamic Combo Chart in Excel


A combination chart is a chart type that has more than one type of chart in a single view. Most times it's a combination of a column and line chart where it would plot something like volume and revenue together. The volume data can be a column chart and the revenue data can be the line chart. Excel has provided this combo chart capability for quite a while but in later version it became very easy as a chart type selection (which this video will cover). However there not really an option to give interaction like selecting to view the column chart alone and then both chart type together with a click of the button. This video will cover a quick hack on how to create a dynamic or interactive combo chart using slicers.

Tuesday, June 2, 2020

Power BI Animated Bar Chart


An animated chart in Power BI is nothing new since you could create an animated bubble chart. But the animation of a bar chart has been something that has been circulated around the Internet in some form or another outside of Microsoft products. You can experience it from Hans Rosling's Gapminder application to the Charticulator tool. This example will use covid-19 data from the New York Times dataset that can be found at github. Though not as entertaining as Trevor Noah's skit on using Excel to Flatten the curve (just search for "Microsoft Excel: Coronavirus Edition"), this hopefully is another edu-taining way to see data.

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