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 →
{ 4 Comments }
I’d like to discuss 2 cases of restructuring layouts of datasets: Case 1 Consider the 7 columns dataset below. For every Doc Number, there are 6 columns – 3 columns for the name of the employee and another 3 columns for the Percent of each employee. Doc Number Emp 1 Emp 1 % Emp 2 […]
Read More →
{ 74 Comments }
In this file, the source data sheet is a pasted special pivot table. The task is to convert this pivot table data layout to the result shown in the “Desired result” sheet. Effectively, we need to convert a matrix like data layout to a tabular layout. We need to “denormalise” the pivot table. Solution for […]
Read More →
{ 60 Comments }
Assume a two column database of approximately 20,000 rows (say from A1:B20237). The first column has vendor names and the second column has Part codes (alphanumeric string). Headings are in A1:B1, say Vendor in cell A1 and Part_code in cell B1. One vendor supplies multiple parts and therefore there would be repetitions of vendor names […]
Read More →
Unpivot data with a formula
{ 1 Comments }