Hello Anmol,

You can use the Lumenore Data Magnet ETL tool to convert rows to columns and columns to rows.
Data Magnet provides two transformations for this purpose:

• Data Pivot → Rows to Columns (Summarisation)
• Data Unpivot → Columns to Rows (Normalisation)

Below is a step-by-step guide for both.

1. Rows to Columns (Data Pivot)

What is Data Pivot transformation?
Data Pivot is used when you want to group similar records and calculate totals, averages, or counts. It is used to convert rows to columns.

Step-by-Step: Data Pivot

Step-by-Step: Data Unpivot
1. Upload your Excel File using the Excel component and connect the Data Pivot component to it.

2: Configure Data Pivot :

• Select Group Field: Select UserID because we want one final row per user.
• Select Pivot Column Field: Select Product. This tells the system how purchases are categorised inside each user group. Here, we choose the columns that aren’t part of the group but will be used for the pivot operation. This defines how the data will be displayed.
• Select Value Field: Select Amount because this column contains the numbers we want to calculate.
• Choose Aggregate Function: Choose SUM to calculate total spending per user.
• Enable –  Is Numeric Fields – Required to apply aggregation on numeric data.

Step 3: Save & run the graph.

2. Columns to Rows (Data Unpivot)

Step-by-Step: Data Unpivot
1. Upload your Excel File using the Excel component and connect the Data Unpivot component to it.
2 Open the component and select unpivot type.

Options in the Dropdown:
1. Select Columns to Keep Unchanged
• You select which columns should stay the same (e.g., ID, Name, Date).
• All other columns will be unpivoted, their column names become values in one column, and their data becomes values in another.

2. Select Only Columns for Unpivot
• You explicitly select the columns you want to unpivot.
• All non-selected columns remain unchanged.

We will be using – ‘Select Columns to Keep Unchanged’:
• In this step, you will choose which columns should remain unchanged during the unpivot process.

By default, all columns are selected automatically in the table. You can manually adjust which columns to keep unchanged by unchecking any that should be affected by the unpivot.
Select Product because it identifies the record.

Next – Enter the Attribute Name:

• This is where you specify the name of the property or label you want to create.
• The attribute creates a new column. For example, if we name it “unpivotdata,” the values in the columns below will now appear as rows in the “unpivotdata” column.

We use the ‘Month’ column. This column will store original column names (Jan, Feb, Mar).

Next – Enter the Value:
• This is where you define the corresponding value for the attribute.
• In our case, we’ve used “Sales” as the value. This represents the data that will be shifted into the new rows created by the attribute.

Next – Unchanged Columns:
• These are columns that will remain the same and won’t be affected by the unpivoting process.

Verify the Table:
• Once you set the attributes and values, check the table where you’ll see all columns being unpivoted.
• Each row in the table represents a field that has been unpivoted, and it will show:
– Field name: The name of the original column from your dataset.
– Type: The value assigned (likely a label or key) in the unpivot result.

Step 3: Save & run the graph.

Summary

• Use Data Pivot when you want to summarize data (rows → columns)
• Use Data Unpivot when you want flexible, row-based data (columns → rows)