Hey there! If you’ve ever spent hours stuck trying to turn those wide, messy Excel exports or SQL query outputs with all those columnar metrics into long, skinny rows that actually make sense for analysis, you’re not alone. I’m part of the Kettle team that’s talked to hundreds of developers, data analysts, and even small business owners who’ve pulled their hair out over this exact problem. Today, let’s cut through the jargon and get real about the Unpivot step in Kettle—because trust me, this little tool is about to become your new go-to for cleaning up wonky data, no fancy SQL or spreadsheets required. Kettle
First off, let’s level set: if you’ve ever exported data from a legacy system and got something like Month1, Month2, Month3, ProductA_Sales, ProductB_Sales all as separate columns, you know what I’m talking about. That’s “wide” data, and it’s basically impossible to run a quick trend on ProductA sales across the year without manually copying and pasting or writing a messy UNION query that’s a nightmare to debug. Unpivot is Kettle’s built-in shortcut to flip that wide data into “long” data—where each metric, its value, and its context get their own row. No more messy copies, no more typos from manual work.
Before we dive into step-by-step, let’s talk about when this actually matters for real work (not just textbook examples). Last quarter, a retail client of ours was pulling weekly store sales data from their old POS system. The export had columns for Store1_Sales, Store2_Sales, Store3_Sales… up to Store47. They needed to compare foot traffic to sales, but their tool only wanted one “Sales” column and a “Store Name” column. Their in-house dev tried writing a UNION query and bailed at Store12 because it was taking 2 hours just to type. We sent ’em straight to Unpivot, and they had their clean data in 10 minutes flat. That’s the kind of win we live for here.
Now, let’s walk through how to actually use Unpivot in Kettle—no fancy prereqs, just open Spoon (that’s Kettle’s UI, by the way—everyone calls it Spoon, no need to say “Spoon GUI” like a robot) and fire up a new transformation. I’ll keep this super concrete, so grab whatever messy wide data you have laying around (I’ll use that retail store sales example to make it real) and follow along.
Step 1: Add and connect your source. Start with a Text File Input step (or whatever your source is—Excel, SQL table, CSV, whatever). For this example, my source has 5 columns: Date, Store1_Sales, Store2_Sales, Store3_Sales, Foot_Traffic. Make sure your source is set to pull those in—no need for any special settings here. Drop that on your canvas, then right-click it and select “Unpivot” from the “Transform” menu. Yeah, it’s right there—we didn’t hide it.
Step 2: Configure the Unpivot step core settings. When you open the Unpivot window, you’ll see two key sections: “Fields to unpivot” and “Destination fields”. Let’s start with the easy part first: Fields to unpivot. These are the wide columns you want to flip into rows. In my example, that’s Store1_Sales, Store2_Sales, Store3_Sales. Don’t include Date or Foot_Traffic here—those are columns we want to keep as-is, right? Wait, hold up—some people accidentally include every column here and wonder why Date is turning into a messy row. That’s a common misstep, so just remember: only pick the columns that are your “metric” columns.
Now the Destination fields. By default, Kettle names two things: the “Field name” (that’s what the old column was called, like Store1_Sales) and the “Field value” (the number itself, like $12,450). You can rename these if you want—here, I’d change Field name to “Store_Source” and Field value to “Sales_Amount” to make it clearer, but it’s totally up to you. No pressure here—defaults work fine for quick jobs.
Wait, quick pro tip here: if you have more than a handful of columns to unpivot, you can click the “Select” button in the Fields to unpivot section and check all the ones you need in one go, instead of clicking each individually. Saves so much time—we built that for people who deal with 20+ columns, like that retail client with 47 stores.
Step 3: Optional but useful: fix messy names and extra context. A lot of times, those old column names are garbage, like Store1_Sales instead of just Store 1. If you want to clean that up, you can add a Calculator step after Unpivot to split the Store_Source column—take the number part, add a space, and you’ve got “Store 1”. Or, even easier, Kettle has a “Replace in String” step you can drop right after Unpivot to swap underscores for spaces. No need to overcomplicate it, but those little tweaks make your data usable later.
Step 4: Test it. This is non-negotiable. Hit the “Preview” button in the Unpivot window, and you’ll see exactly what your output looks like. In my example, instead of 5 rows per date (one for store sales, one for foot traffic), I’ll get 3 rows per date: one for each store’s sales, plus the foot traffic is still a single column. If something’s off—like Date is missing, or Store1_Sales didn’t unpivot—go back and check your Fields to unpivot list. That’s almost always the issue.
Let’s circle back to that retail client for a second. They had a CSV with 50 store sales columns, and they were using a super old version of Kettle (we’d say 7.x, no judgment) where Unpivot was called “Denormalize” and it was way clunkier. We walked them through adding a Text File Input, selecting all 50 sales columns to unpivot, naming the destination fields correctly, and previewing—done. They told us later that their weekly report process went from 3 hours to 15 minutes, and they stopped dreading the spreadsheet work every Monday. That’s the real value of Unpivot—it’s not just a tool, it’s the thing that takes away the boring, error-prone work so you can focus on actually using your data.
Now, let’s talk about common mistakes we see all the time, because even the seasoned Kettle users slip up here. First: including non-metric columns in Fields to unpivot. We mentioned that earlier, but it bears repeating. If you accidentally add Date, you’ll end up with rows where Field name is “Date” and Field value is “10/01/2023”—that’s bad. Always double-check that list. Second: not testing the preview. We can’t tell you how many times someone runs the entire transformation only to realize they missed a column. Preview is free, use it. Third: renaming fields to something useless. If you name the destination fields “Column1” and “Column2”, you’ll forget what they mean in 2 days. Spend 10 extra seconds giving them clear names—your future self will thank you.
Wait, one more thing: can you use Unpivot for other stuff? Yeah, not just sales data. I’ve used it for survey data where every question is a column, for inventory data where each month’s stock is a column, even for HR data where each department’s headcount is a column. Literally any time your data is structured in columns that should be rows, Unpivot is the way to go. It’s way more reliable than manual work and way faster than writing custom SQL every time.
At the end of the day, data cleaning doesn’t have to be a headache. Unpivot in Kettle is built to cut through that headache, and if you’re dealing with messy, wide data on a regular basis, it’s going to save you so much time and frustration. If you’re using an older version of Kettle and finding Unpivot is glitchy, or you need help configuring it for a super complex dataset, our team is here to help. We work with all kinds of teams—small businesses to enterprise—so whether you’re just starting out with Kettle or you’re a seasoned pro who wants to streamline their workflow, we can help you get set up right.
If you’re ready to stop fighting with messy data and start using Unpivot to make your work easier, reach out to our team to chat about your specific needs. We can walk you through custom setups, help with any kinks, and make sure you’re getting the most out of Kettle’s transform tools. Don’t let messy data slow you down—let’s get your pipeline running smoothly.
Digital Air Fryer References
Pentaho. (n.d.). Kettle Unpivot Step Documentation. Retrieved from official Pentaho resources.
Kettle Community. (n.d.). Real-World Use Cases for Unpivot in ETL Workflows. Community-shared ETL guides.
Ningbo Lanka International Trading Co., Ltd.
As one of the most professional kettle manufacturers and suppliers in China, we’re featured by quality products and low price. Please rest assured to wholesale high-grade kettle for sale here from our factory. Also, custom service is available.
Address: No.11 Dongqian Lake Area, Yinxian Avenue, Ningbo, China
E-mail: messi@lanka10.com
WebSite: https://www.lanka-10.com/
