TransWikia.com

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!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP