Category: POWER QUERY

Return best possible fit, to manually entered dimensions, with the intent to minimise wastage

{ 5 Comments }

Refer to a simple 5 column representative inventory dataset of a Glass manufacturer: Model Length (MM) Wide (MM) Thk (MM) CAT HX9-G-ARD 1071 273 3.5 A MYP-G-3RD 580 535 3.2 B EPO-G-3RD 580 535 3.2 A MYG-G-3R 966 350 3.2 A MYN-G-3RD 649 530 3.2 A GM SPIN-G-3FD 882 395 3.2 A MY8-G-AR 880 400 3.5 B […]

Read More →

Generate a list of assignees for different projects based on a competency matrix

{ 4 Comments }

Here is a small sample of a Project matrix which shows tasks to be accomplished for various projects.  There can only be upto 6 tasks per project. Project Name Task1 Task2 Task3 Task4 Task5 Task6 Project1 Painting Chef Gardener Project2 Tiling Digging Engineering Project3 Mechanic Engineering Here is a competency matrix showing the competencies of employees on […]

Read More →

Sales data modelling and interactive visualisations of an E-Commerce Company

{ 11 Comments }

In this workbook, I have Sales data of an E-Commerce Company for 3 months.  The typical columns in the base data are: 1. Order Date/Time 2. City to which orders were shipped 3. Order Number 4. Payment Type i.e. Cash on delivery, Net Banking, EMI’s 5. Order Status i.e. Delivered or cancelled 6. SKU’s which the […]

Read More →

Show multiple text entries in one cell of a Pivot Table

{ 0 Comments }

Imagine a three column dataset as shown below ID Prize Year A X 9 A Y 10 B X 9 B Y 9 B Z 10 As you can observe, there are duplicates that appear in each column.  The task it to convert this tabular data structure to a matrix like one with ID’s appearing […]

Read More →

Transpose data column wise

{ 0 Comments }

Here’s a simple two dataset: Project ID Employee A00 Ashish Mathur A00 Henry A00 Paul A00 Sanjay Singh L02 Geeta Puri L02 Ajay Singh L02 Robert L02 Mike L02 Ajay Kumar Venkatesh L02 Ashish Mathur L02 Geeta Kukreja A00 Sameer Mehta L02 Harish Rai As one can observe, there are repetitions in the Project ID column.  […]

Read More →

Create a daily work schedule

{ 2 Comments }

Imagine a 4 column dataset as shown below: Resource Name Start End Projects Raghav 02-06-2015 05-06-2015 p4 Raghav 02-06-2015 07-06-2015 p5 Raghav 08-06-2015 12-06-2015 p1 Raghav 16-06-2015 19-06-2015 p2 Raghav 22-06-2015 26-06-2015 p3 Ashish 03-06-2015 09-06-2015 p6 Ashish 04-06-2015 07-06-2015 p7 One may want to transform this dataset to the below mentioned one.  There will […]

Read More →

Remove duplicates from rows

{ 0 Comments }

Assume a simple matrix like data structure as shown below.  As one can observe, for every unique ID, there are duplicate traits appearing.  The objective is to remove duplicate traits from each row.  The Expected solution is show in the second table. Unique ID Trait1 Trait2 Trait3 Trait4 Trait5 a 1 1 2 b 1 […]

Read More →

Flip a string with conditions

{ 2 Comments }

Assume a column with some IP addresses.  Each cell has 4 portions separate by a dot.  The number of digits in each portion can either be 2 or 3.  The following tasks need to be performed on this dataset: 1. Drop the last portion of the string 2. Flip the string 3. Add the following text to […]

Read More →

Consolidate multiple rows of data and remove blank rows

{ 3 Comments }

Imagine a dataset representing questions answered by various respondents in a survey.  The first column has the Respondent’s Name and thereafter there is one column each for a question posed in the survey.  The data extracted from the system suffers from the following fallacies: 1. There are blank rows; and 2. Multiple questions answered by […]

Read More →

Merge data from multiple cells into a single cell

{ 10 Comments }

Assume a simple two column dataset with Name in column A and Department in column B.  There are repetitions in the Name column.  Here’s a snapshot of the base data: One may want to merge data from multiple cells into a single cell – basically a view where all unique names are listed in a […]

Read More →