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 of Power Query is available here. However, if you are a Microsoft 365 user, then the new array shaping functions can be used to get the same result. The expected result has been obtained by using the array shaping functions of Microsoft 365. The input dataset and expected result is shown below:
You may download the solution workbook from here. As one adds further rows of data to the input data range, the result in the expected result range will update.
Unpivot data with a formula
{ 1 Comments }