Author: Ashish Mathur

Unpivot data with a formula

{ 1 Comments }

Many a times, one may want to convert a column expanding data to a row expanding one (also referred to as “Unpivoting/flattening a dataset”. The easiest way to do so is with the usage of Power Query (referred to as the Query Editor in some versions). The technique to unpivot a dataset with the help […]

Read More →

Determine latest condition of each equipment and show a month wise count

{ 1 Comments }

There are 100 machines in a factory.  Every machine has different test frequency. In a given month, not every machine is tested but we still have the last known rating (from some previous month) of that machine.  We have to show the latest rating of each machine for each month in a stacked column chart. […]

Read More →

After filtering a dataset, allow the user to display only specific columns in the result

{ 2 Comments }

Here is a simple 5 column dataset showing basic employee information. The objective is to show only those rows of data in which the Score (column D) is greater then 3.  While one can solve this with a simple filter, the solution will not be dynamic.  To get a dynamic solution, one may use the […]

Read More →

Show Balance outstanding everyday even if data for everyday is not available

{ 1 Comments }

In this simple 3 column dataset, there are 2 accounts – Konto 1 and Konto 2.  Each account has a balance outstanding as on a certain date.  However, if you notice carefully, there is no balance for any account on January 4-5,9-10 2020. The objective is show the balance outstanding every day.  For days which […]

Read More →

Analyse membership changes from year to year

{ 2 Comments }

Assume a simple 4 column dataset as shown below.  This data shows which ID had which type of subscription in which year.  So ID A, which started as a “Free” subscriber in 2018 switched to a “Premium” subscriber in 2019 and then churned out in 2020.  Likewise, ID D which started as a “Pro” subscriber […]

Read More →

Segregating data appearing in a single column into multiple columns where there is blank row between records

{ 0 Comments }

Here is a sample dataset downloaded from an external application.  As you can observe, there are anomalies in the dataset: All the data appears in a single column when Infact it should be segregated into different columns; and There is no separating one record from another.  All we know is that each record ends with […]

Read More →

Show text entries in the value area section of a Pivot Table after meeting certain conditions

{ 0 Comments }

In the value area section of a normal Pivot Table one can only show the result of aggregation functions such as SUM(), COUNT(), AVERAGE() etc.  Even if one drags a text field to the value area section of a Pivot Table, one cannot show those text fields because they automatically get counted. Consider the following […]

Read More →

Count tasks by status

{ 0 Comments }

Assume a simple 3 column dataset as shown below – the date of each task and the status of that task. The objective is to get the status wise count of tasks by the last time stamp.  So for the Status “To-do”, the count should be 2 – Task ABC and DEF.  Only these two […]

Read More →

Segment towns according to volume contribution and market share with a slicer

{ 0 Comments }

This post is an extension to the one I posted here – Segment towns according to volume contribution and market share. Here’s a simple dataset of Shampoo sales in the state of Rajasthan, India. For a chosen segment, one may want to segment the 4 towns based on the following conditions: Based on the two screenshots […]

Read More →

Segment towns according to volume contribution and market share

{ 0 Comments }

Here’s a simple dataset of Shampoo sales in the state of Rajasthan, India. For a chosen segment, one may want to segment the 4 towns based on the following conditions: Based on the two screenshots shared above, the desired result is shown in the screenshot below: The desired result is shown in range E16:E19 and […]

Read More →