The workflow at a glance
Identify each output by source row, product and month. Keep units and sales_usd from the same month together, preserve explicit zeros and mark the blank quantity as missing. Accept the result only after checking six records, cell-level provenance and monthly reconciliations.
Define the grain of one output record
One long-form record represents one product from one source row in one month. Preserve source_row_id and product_id. The allowed months are 2026-09 and 2026-10; units counts items, while sales_usd contains dollar sales amounts. These are supplied values, so no exchange-rate or tax calculation is required.
Microsoft’s Unpivot documentation describes turning columns into attribute-value pairs. Our task adds a separate pairing step: put the two metrics for the same month back into one record. An Attribute/Value result alone is not the required six-row, two-metric delivery.
Write down those meanings before asking AI to reshape anything. For a genuinely mixed-currency report, first follow the separate currency grouping and reconciliation method; similar-looking numbers do not establish a shared unit.
| Field | Meaning and constraint |
|---|---|
| source_row_id / product_id | Copy source identifiers; do not drop IDs or merge same-name products. |
| month | Derive it from an allowed source heading, not today’s date. |
| units / sales_usd | Values from the same source row and month, with different units. |
| units_status | observed for an explicit source quantity; missing for a blank quantity. |
Three source rows, with an explicit zero and a blank
Copy this entire CSV as the input. P-02 explicitly has zero units and zero sales in September. P-03 has 75 dollars of October sales but no quantity. No rule states that its unit price is constant or permits a quantity to be inferred.
The task is structural transformation, not business diagnosis. Explaining the blank or validating the business transaction requires source records outside this exercise. For now, preserve exactly what is available; the separate guide on missing values versus zero explains that distinction.
source_row_id,product_id,2026-09_units,2026-09_sales_usd,2026-10_units,2026-10_sales_usd
r1,P-01,4,80,6,120
r2,P-02,0,0,3,90
r3,P-03,2,50,,75Map the four full headings before reshaping
Do not pair numbers merely by their positions. Establish which month owns each record and which metric owns each value. The sales_usd metric contains an underscore, so splitting a heading at the first underscore is not a complete metric parser.
Use this explicit allowlist for the exercise. A later 2026-11 column or reviewer_note field needs a deliberate decision and a revised mapping; it must not silently become another sales measure.
| Source heading | Target month | Target field |
|---|---|---|
| 2026-09_units | 2026-09 | units |
| 2026-09_sales_usd | 2026-09 | sales_usd |
| 2026-10_units | 2026-10 | units |
| 2026-10_sales_usd | 2026-10 | sales_usd |
Specify pairing, missing values and stop conditions
Supply the output contract, mapping and complete CSV together. Ask AI to explain its mapping, produce the CSV and then reconcile each month. That separates interpreting the headings from copying the values correctly.
If you use file input, also confirm which sheet and header row the product actually read. This tutorial assumes no particular upload feature: its short input can be pasted into an AI conversation that accepts text.
Reshape the following fictional product CSV. Do not add business facts.
One output record represents one source row, one product and one month. Preserve source_row_id and product_id.
Use only these mappings: 2026-09_units -> 2026-09/units; 2026-09_sales_usd -> 2026-09/sales_usd; 2026-10_units -> 2026-10/units; 2026-10_sales_usd -> 2026-10/sales_usd.
Output columns: source_row_id,product_id,month,units,sales_usd,units_status.
Keep an explicit 0 as 0. Leave a blank quantity empty and mark it missing; mark other quantities observed. Never infer a quantity from an earlier unit price, nearby row or sales amount.
If headings do not match, source-row IDs repeat or an unagreed month appears, list the problem and stop instead of silently repairing it.
Return all six CSV records. Then report monthly record counts, known-unit sums, missing-quantity counts and sales totals. A known-unit sum is not a complete quantity total when values are missing.
Explain how to recompute each check from the source rows.
Source CSV:
source_row_id,product_id,2026-09_units,2026-09_sales_usd,2026-10_units,2026-10_sales_usd
r1,P-01,4,80,6,120
r2,P-02,0,0,3,90
r3,P-03,2,50,,75Reference answer: six records with the metrics still paired
Check individual records before totals. Keep the September P-02 record even though both values are zero. Keep the October P-03 record with an empty quantity, sales of 75 and a missing status.
Row order may differ. Each source_row_id, product_id and month combination must remain unique, with values traceable to the corresponding source cells. This order makes comparison with the three input rows straightforward.
source_row_id,product_id,month,units,sales_usd,units_status
r1,P-01,2026-09,4,80,observed
r1,P-01,2026-10,6,120,observed
r2,P-02,2026-09,0,0,observed
r2,P-02,2026-10,3,90,observed
r3,P-03,2026-09,2,50,observed
r3,P-03,2026-10,,75,missingmonth,record_count,units_known_sum,units_missing_count,sales_usd_sum
2026-09,3,6,0,130
2026-10,3,9,1,285Recompute both measures and repair a plausible wrong row
September units are 4+0+2=6 and sales are 80+0+50=130. October’s known units are 6+3=9, with one missing quantity; its sales are 120+90+75=285. Sales across both months total 415, while the complete combined unit total remains unknown.
Swapping two sales amounts between months could preserve the grand total of 415. A correct grand total is therefore only one check: every source row, month and metric must also point to the right source cell.
The wrong quantity below comes from 75 divided by (50 divided by 2), assuming P-03 keeps the same unit price across both months. The exercise supplies no such assumption. Correct arithmetic does not turn that estimate into source data.
A sales amount does not authorize an inferred quantity
r3,P-03,2026-10,3,75,observed
r3,P-03,2026-10,,75,missing
Deliver the source, mapping and exceptions together
Keep the original CSV, six-row result, four-column mapping and monthly reconciliation in the delivery. A colleague should be able to reconstruct the result cell by cell without depending on the narrative in one AI conversation.
For three rows, manual reshaping is also practical. For recurring month columns, an appropriate spreadsheet application can hold a repeatable transformation, with this case retained as a regression sample. This article has not verified an automatic wide-to-long feature in KitNelo and does not present another application’s method as a KitNelo button.
Checks before publishing or handing over the result
- Three source rows each produce two month records, giving six records with intact identifiers.
- September P-02 retains both explicit zeros rather than disappearing as an empty record.
- October P-03 keeps its quantity blank; the missing-quantity count is one.
- Monthly sales equal 130 and 285, and each source-cell mapping also agrees.
- October’s 9 is labeled a known-unit sum; its complete quantity total is unknown, with source and mapping retained.
Common questions
Should two source rows for the same product be merged automatically?
No. Preserve different source-row IDs during reshaping and aggregate them later under an explicit business rule. If the source-row ID itself repeats, first resolve whether the import is duplicated or the ID rule is unsuitable.
What if the source uses merged month headings?
Prepare complete, unique column headings first and confirm which month and metric each column represents. Do not ask AI to guess which month a blank header inherits from the visual layout; retain the evidence for the corrected mapping.
