What is the processed called when converting data in repeated columns to rows on a spreadsheet?

Super User Asked on December 25, 2020

If I have a spreadsheet with a data structure where similar information is repeated across multiple columns like so:

TxID     Date      Merchant   Item 1 Name   Item 1 Value   Item 2 Name   Item 2 Value, etc...
0000001  1/1/2020  Macy's     Jeans         $50.00         Socks         $10.00

What is the name of the process that converts the data to have similar data presented in a single column, with information repeated across multiple rows as needed:

TxID     Date      Merchant   Item Name     Item Value
0000001  1/1/2020  Macy's     Jeans         $50.00         
0000001  1/1/2020  Macy's     Socks         $10.00

Having data in the second format is much easier to process, as it guarantees specific information is in a single column, e.g. to sum all sales for socks, I would only need to check a single Item Name and Item Value columns, rather than searching across multiple columns.

I can’t recall what this process is called, which makes it difficult to find resources on how to do it effectively. My first though was this is similar to database denormalisation, however that requires data to be normalised first, so I don’t think that’s the correct term. Transpose also didn’t seem correct, as it seems to refer to rearranging all data from columns to rows, rather than just a subset.

Is there a term for this type of data processing?

One Answer

Process is called UNPIVOT.

Here is some script for GoogleSheets.

Here is example of using Power Query in MS Excel

Answered by basic on December 25, 2020

Add your own answers!

Related Questions

Ubuntu Mate freezes after user login

1  Asked on November 9, 2021 by nathaniel-graham


Keep getting “Index out of range” error

1  Asked on November 9, 2021 by jerunenchaussettes


How does Iconv handle endianness?

1  Asked on November 9, 2021


MSI BIOS Distorted

0  Asked on November 9, 2021 by karma-blackshaw


Network problems using WSL2

3  Asked on November 7, 2021 by lars-rdal


With Bash + iTerm2, how to name tabs?

16  Asked on November 7, 2021


How to increase the font size for Skype on OS X?

4  Asked on November 7, 2021 by user2225404


How can I stop photos I download from popping up in Preview?

2  Asked on November 7, 2021 by mike-mercer


Ask a Question

Get help from others!

© 2022 All rights reserved. Sites we Love: PCI Database, MenuIva, UKBizDB, Menu Kuliner, Sharing RPP