MELT

You are here:

AXCEL.MELT function

This function melts or unpivot your data. In many cases, data for each identity is presented in rows which is not suitable for consuming in the model or visualization. MELT makes this process easy for you by restructuring your data in columns.

Syntax

AXCEL.MELT(data, id_columns, value_columns)


The AXCEL.MELT function syntax has the following arguments:

Data Required. This is the input which needs to be unpivot.

id_columns Required. This variable lists the name of columns to be used for unpivoting. It could be a single column name or a vector of multiple columns.

value_columns Required. This is a list of columns to be extracted from the table and assign to each group.

Example:

As an example, the ERP system in a company reports its monthly sales for products A, B and C in this table:

product 1/1/20 2/1/20 3/1/20 4/1/20 5/1/20 6/1/20
A504492971815011189053683471671
B91353676172723075647514966418
C267076807519984354691435578100

As you can see, this data is not suitable to be used for modeling as a cross-section or a panel data. As a result, this data needs to be transferred to a columnar structure. To do so, we can simply run the MELT function as follows (consider data is located at A1:G4):

=AXCEL.MELT(A1:G4,A1,B1:G1)

In fact, A1:G4 is the location of data, A1 defines the identification of each product which is located at A1 (product) and monthly data are in columns B to G which have the headers at B1 to G1. After running the function, you get this result:

product variable value
A1/1/2050449
B1/1/2091353
C1/1/2026707
A2/1/2029718
B2/1/2067617
C2/1/2068075
A3/1/2015011
B3/1/2027230
C3/1/2019984
A4/1/2018905
B4/1/2075647
C4/1/2035469
A5/1/2036834
B5/1/2051496
C5/1/2014355
A6/1/2071671
B6/1/206418
C6/1/2078100

As you can see, data is restructured in three columns. Each row presents the sales for each product A, B or C in Months January to June in 2020. The data is ready to be used for other functions or visualization.

See other data management function.