How to unpivot wide excel sales data to long format for power bi import
- Step 1Export the wide sales/budget sheet — Save the finance file as
.xlsx(or.csv). Make sure the wide table is on the first sheet — the tool reads sheet index 0 only. If the data sits on a 'Pivot' or 'Summary' tab, copy it to the first tab first. - Step 2Identify ID columns vs. period columns — ID columns are the dimensions you keep per row —
Region,Product,Cost Centre. Period columns are the wide headers to melt —2021, 2022, 2023orJan, Feb, ..., Dec. Note their exact header text. - Step 3Fill the option fields — Type the dimensions into ID columns (comma-sep), the period headers into Value columns (comma-sep), set Variable col name to
Year(orMonth/Period), and Value col name toRevenue(orSales/Amount). - Step 4Run and download unpivoted.xlsx — Click run; the tool reshapes in the browser and offers
unpivoted.xlsxwith a singleUnpivotedsheet. Confirm the row count equals input rows × number of period columns. - Step 5Import into Power BI — In Power BI Desktop: Get Data → Excel workbook → select the
Unpivotedsheet → Load. The Year/Month column is now a single field you can mark as a date or join to a date table. - Step 6Build the date axis and measures — If the variable column holds bare years, set its data type to whole number or text and relate it to a calendar table. For full dates, format the column then use it directly in time-intelligence DAX (DATESYTD, SAMEPERIODLASTYEAR).
Wide-to-long for BI: the four fields
The complete option set. There is no auto-detect of which columns are years, no preset for 'months', and no aggregation — you name the columns explicitly.
| Field | Typical BI value | Result |
|---|---|---|
| ID columns (comma-sep) | Region, Product | Dimensions repeated on every output row |
| Value columns (comma-sep) | 2021, 2022, 2023 | Each becomes a row; its header text lands in the variable column |
| Variable col name | Year | The field Power BI binds to a date axis / calendar relationship |
| Value col name | Revenue | The measure column you aggregate in DAX |
Why BI tools need long format
What breaks in Power BI / Tableau when data stays wide, and what the reshape fixes.
| BI need | Wide format (columns) | Long format (rows) after unpivot |
|---|---|---|
| Continuous date axis | Impossible — each year is a separate field | One Year/Month column to put on the axis |
| Time-intelligence DAX (YoY, YTD) | No date column to evaluate against | Works once the variable column is dated |
| Adding a new period | New column → every visual and measure must be rewired | New rows only — visuals update automatically |
| Single measure to aggregate | N year columns to SUM separately | One Revenue column aggregated by Year |
Tier limits and the row-multiplication trap
Output rows = input rows × period columns. Plan against the input-row cap for your tier (the cap is on input rows).
| Tier | Max file size | Input row limit | Example: 12 months from N rows |
|---|---|---|---|
| Free | 5 MB | 10,000 | Un-Pivot is Pro-only |
| Pro | 50 MB | 100,000 | Up to 100,000 input rows -> up to 1.2M output rows |
| Pro-media | 200 MB | 500,000 | Up to 500,000 input rows |
| Developer | 500 MB | Unlimited | No row cap |
Cookbook
Reshapes aimed at a Power BI / Tableau import. Each shows the wide finance input, the four field values, and the long output ready for Get Data.
Annual sales by region, melted for a date axis
Three year columns become a Year column Power BI can relate to a calendar table. Output = 2 rows × 3 years = 6 rows.
Input (wide, from finance): Region,Year_2022,Year_2023,Year_2024 North,500000,540000,580000 South,420000,455000,490000 Fields: ID columns: Region Value columns: Year_2022, Year_2023, Year_2024 Variable col: Year Value col: Revenue Output (unpivoted.xlsx): Region,Year,Revenue North,Year_2022,500000 North,Year_2023,540000 North,Year_2024,580000 South,Year_2022,420000 South,Year_2023,455000 South,Year_2024,490000
Monthly columns (Jan..Dec) to a Month column
Month-name headers melt into a single Month column. The header text lands verbatim in the variable column, so Jan/Feb/... become the Month values — relate them to a month-number lookup for proper ordering in visuals.
Input (wide): Product,Jan,Feb,Mar Widget,1200,1350,1500 Fields: ID columns: Product Value columns: Jan, Feb, Mar Variable col: Month Value col: Units Output: Product,Month,Units Widget,Jan,1200 Widget,Feb,1350 Widget,Mar,1500
Keep two dimensions for slicers
List Region and Product as ID columns so both stay on every row and can drive Power BI slicers. The order you type ID columns is the order they appear in the output.
Input (wide): Region,Product,Q1,Q2,Q3,Q4 West,Widget,10,12,14,16 Fields: ID columns: Region, Product Value columns: Q1, Q2, Q3, Q4 Variable col: Quarter Value col: Units Output: Region,Product,Quarter,Units West,Widget,Q1,10 West,Widget,Q2,12 West,Widget,Q3,14 West,Widget,Q4,16
A subtotal column you must exclude
Finance sheets often carry a Total column. Do not list it as a value column — if you do, your Revenue measure double-counts (period rows + a Total row). Leave Total out of both boxes so it is dropped, then let Power BI compute totals.
Input (wide, with a Total): Region,2022,2023,Total North,500,540,1040 Fields (Total deliberately omitted): ID columns: Region Value columns: 2022, 2023 Variable col: Year Value col: Revenue Output (no Total row -> no double-count): Region,Year,Revenue North,2022,500 North,2023,540
Strip the Year_ prefix before unpivoting
The variable column gets the header text exactly, so Year_2022 stays Year_2022 — not ideal for a date relationship. Rename the headers to bare years first (with the header sanitizer), then unpivot, so the Year column holds 2022, 2023, 2024.
Step 1 (header rename, sibling tool): Year_2022 -> 2022, Year_2023 -> 2023, Year_2024 -> 2024 Step 2 (unpivot): ID columns: Region Value columns: 2022, 2023, 2024 Variable col: Year Value col: Revenue Output Year column now holds clean years: Region,Year,Revenue North,2022,500000 North,2023,540000
Edge cases and what actually happens
Power BI cannot build a date axis after import
Data type issueThe reshape gives you a single Year/Month column, but Power BI still needs it typed correctly. Bare years (2022) import as text or whole number, not date — relate them to a calendar table or convert. Header text like Year_2022 will not parse as a date at all; rename headers to bare years before unpivoting. For an actual date column that melted to text, normalise it with the date standardizer.
A Total column got melted into rows
Double-count riskIf you list a subtotal/Total column among the value columns, it becomes data rows and your aggregated measure double-counts. Omit Total (and any grand-total row) from both boxes so it is dropped, and let Power BI compute totals from the period rows.
Output row count multiplied unexpectedly
ExpectedOutput rows = input rows × number of period columns. A 5,000-row sheet with 12 months becomes 60,000 rows — correct. Check it against the Pro 100,000-input-row cap; the cap applies to input rows, so this reshape fits, but a 100,000-row monthly sheet would not.
Empty period cells become empty rows
PreservedA missing month value still emits a row with an empty Revenue cell — the melt never skips blanks. In Power BI these show as (Blank). Filter them in Power Query after load if you want a sparse model, or leave them since blanks do not affect SUM.
Period headers do not match what you typed
Empty value blockMatching is by exact header text after trimming. If you type 2022 but the header is FY2022, that column yields empty values for every row. A block of blanks in one period is the tell-tale of a mistyped value-column name.
Data is on a non-first sheet
Not readOnly the first sheet is processed. A wide table on a 'Detail' or 'Pivot' tab is ignored. Move it to the first tab before uploading, or the output will be empty.
File too large for the tier
RejectedFiles above the tier size limit (Pro 50 MB) or over the input-row cap are rejected before reshaping. Large monthly extracts can hit this fast; split the source or upgrade (Developer is unlimited).
Free account hits the Pro gate
Requires ProUn-Pivot requires Pro tier; a free account gets 'Un-Pivot requires Pro tier'. BI prep is exactly the workflow Pro targets — upgrade to reshape, or do the unpivot in Power Query if you already have Desktop.
Currency/number formatting in cells
By designNumbers are read with their display formatting applied, so a cell shown as $540,000 may melt as that formatted string rather than the raw number. Clear cell formatting (General number) in the source before exporting if you need clean numerics for DAX.
Frequently asked questions
Why can't Power BI just chart my year columns directly?
Power BI binds a date axis and time-intelligence DAX to a single column of values, not to N separate year columns. Wide data has no date column to evaluate against. Unpivoting collapses the years into one Year column that Power BI can put on an axis and feed to DATESYTD, SAMEPERIODLASTYEAR, and YoY measures.
Why use this instead of Power Query's Unpivot Columns?
Power Query is the right tool if you need a repeatable, auto-refreshing transform inside the model — but it needs Excel/Power BI Desktop and leaves a refresh step. This tool runs in any browser, needs no install, and produces a static .xlsx with no embedded query — ideal for one-off prep or on a Chromebook/iPad.
What should I name the variable column for Power BI?
Name it to match your model field — Year, Month, or Period. The cell contents are the original header text, so for a clean date relationship rename your wide headers to bare years (2022) before unpivoting rather than leaving Year_2022.
Does it handle monthly columns (Jan..Dec)?
Yes — any set of headers can be value columns. Month names become the values in your Month column. For correct chronological sorting in visuals, relate them to a month-number lookup, since alphabetical sort would order Apr before Jan.
Will a Total or subtotal column cause problems?
Yes, if you melt it. A Total column listed as a value column becomes data rows and your aggregated measure double-counts. Leave Total out of both boxes so it is dropped, and let Power BI compute totals from the period rows.
Does the output contain a live connection to my source?
No. The output is a clean static unpivoted.xlsx with a single Unpivoted sheet and no embedded Power Query. You import it via Get Data → Excel like any workbook; there is no refresh button or connection to maintain.
How many rows will I get?
Input rows multiplied by the number of period columns. Twelve months on 1,000 rows gives 12,000 rows. Confirm this stays under your tier's input-row cap (Pro 100,000 input rows) before running.
Can I reshape for Tableau too?
Yes — the long output is exactly what Tableau wants (it even has its own Pivot feature for the same purpose). The same unpivoted.xlsx works for Power BI, Tableau, pandas, and R without modification. If you ever need to go the other way (long back to wide for a summary), use the pivot generator.
Is my finance data uploaded to a server?
No. The reshape runs in your browser via SheetJS. Sales and budget figures never leave the tab — only an anonymous processed-file counter is recorded for dashboard stats, which you can opt out of.
My years export as 2022.0 or with a currency symbol — why?
Numbers and dates are read with their display formatting applied. A year stored as a number with decimals, or a value formatted as currency, melts as that formatted string. Set the source cells to General/plain number before exporting if you need raw numerics.
Can I keep more than one dimension column?
Yes — list every dimension in the ID columns box, comma-separated (e.g. Region, Product, Channel). They all repeat on every melted row and can drive Power BI slicers. The order you type them is the order they appear.
Can I run this on a schedule for monthly refreshes?
Yes. GET /api/v1/tools/excel-unpivot returns the schema; pair the @jadapps/runner once and dispatch the reshape locally — JAD's API never receives the file. A typical flow: monthly finance export -> runner unpivot -> drop into the Power BI source folder.
Privacy first
Every JAD Excel tool runs entirely in your browser using SheetJS and ExcelJS. Your spreadsheets, formulas, and data never leave your device — verified by zero outbound network requests during processing.