Unpivot April-to-March month columns into dated rows in Power Query

The shape finance sends and the shape analysis needs

Budgets, targets and forecasts are nearly always circulated wide: one row per branch and cost head, twelve month columns running Apr to Mar, often a Total at the end. People read that layout easily. Power BI, pivot tables and joins to actuals need it long: one row per branch, head and month, with a date. Until it is long, comparing budget with actual means twelve separate lookups, and a filter on "Q3" has nothing to filter on.

The month labels are the trap. "Jan" in an FY 2026-27 sheet means January 2027, not January 2026. Any conversion that attaches the calendar year of the sheet's first month to every column puts the last quarter of the year twelve months early, and a budget-versus-actual report then compares next year's January budget with this year's January sales.

The M code

In Power Query, load the sheet as a table and replace the generated steps with the following. FYStart is the calendar year in which the financial year begins.

let
    Source   = Excel.CurrentWorkbook(){[Name = "Budget"]}[Content],
    NoTotal  = Table.RemoveColumns(Source, {"Total"}, MissingField.Ignore),
    Long     = Table.UnpivotOtherColumns(NoTotal, {"Branch", "Head"}, "Month", "Budget"),
    Months   = {"Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec","Jan","Feb","Mar"},
    FYStart  = 2026,
    AddNo    = Table.AddColumn(Long, "FY Month No", each
                   let p = List.PositionOf(Months, [Month])
                   in if p < 0 then error ("Unknown month column: " & [Month]) else p + 1,
                   Int64.Type),
    AddDate  = Table.AddColumn(AddNo, "Month Start", each
                   let c = Number.Mod([FY Month No] + 2, 12) + 1
                   in #date(FYStart + (if c <= 3 then 1 else 0), c, 1), type date),
    AddFY    = Table.AddColumn(AddDate, "FY", each
                   "FY " & Text.From(FYStart) & "-" & Text.End(Text.From(FYStart + 1), 2), type text),
    Typed    = Table.TransformColumnTypes(AddFY, {{"Budget", type number}})
in
    Typed

Two lines do the protective work. MissingField.Ignore lets the query run on a copy of the sheet that has no Total column. The error inside the month lookup makes the refresh fail with a readable message if someone adds a column such as "Remarks" or renames "Sep" to "Sept". Without it, List.PositionOf returns −1, the arithmetic quietly produces a March date, and the extra column's contents are added to March. We could not execute M on this machine, so the Python version below is the tested reference for the same logic; compare the two on your data before relying on either.

The same reshape in pandas, with its controls

The budget figures are synthetic.

import pandas as pd

# Synthetic FY 2026-27 budget in the wide layout finance teams send out:
# one column per month from April to March, plus a Total column somebody added.
wide = pd.DataFrame({
    "Branch": ["Noida", "Noida", "Lucknow"],
    "Head": ["Sales", "Travel", "Sales"],
    "Apr": [1200000, 40000, 800000], "May": [1250000, 40000, 820000],
    "Jun": [1180000, 35000, 790000], "Jul": [1300000, 45000, 860000],
    "Aug": [1320000, 45000, 880000], "Sep": [1400000, 50000, 900000],
    "Oct": [1650000, 60000, 1050000], "Nov": [1500000, 55000, 980000],
    "Dec": [1350000, 45000, 900000], "Jan": [1280000, 40000, 850000],
    "Feb": [1260000, 40000, 840000], "Mar": [1600000, 70000, 1000000],
    "Total": [16290000, 565000, 10670000],
})
FY_START_YEAR = 2026            # FY 2026-27 runs 1 Apr 2026 to 31 Mar 2027

MONTHS = ["Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec", "Jan", "Feb", "Mar"]
id_cols = ["Branch", "Head"]

# 1. Prove the Total column is just the sum, then drop it so it is not unpivoted as a month.
assert (wide[MONTHS].sum(axis=1) == wide["Total"]).all(), "Total column disagrees with months"
long = wide.melt(id_vars=id_cols, value_vars=MONTHS, var_name="month", value_name="budget")

# 2. Turn the month label into a real first-of-month date inside the right FY.
fy_month = long["month"].map({m: i + 1 for i, m in enumerate(MONTHS)})       # Apr=1 ... Mar=12
cal_month = (fy_month + 2) % 12 + 1                                         # Apr=4 ... Mar=3
cal_year = FY_START_YEAR + (cal_month <= 3).astype(int)
long["month_start"] = pd.to_datetime(dict(year=cal_year, month=cal_month, day=1))
long["fy"] = f"FY {FY_START_YEAR}-{str(FY_START_YEAR + 1)[-2:]}"
long["fy_month_no"] = fy_month
long = long.sort_values(id_cols + ["fy_month_no"]).reset_index(drop=True)

print(long.head(3).to_string(index=False))
print(long[long["month"].isin(["Dec", "Jan", "Mar"])].head(3).to_string(index=False))

# 3. Controls: row count and grand total survive the reshape.
assert len(long) == len(wide) * 12
assert long["budget"].sum() == wide["Total"].sum() == 27525000
assert long["month_start"].min() == pd.Timestamp("2026-04-01")
assert long["month_start"].max() == pd.Timestamp("2027-03-01")
print("rows:", len(long), "| total:", long["budget"].sum())

Three rows become thirty-six. December 2026 is followed by January 2027 in the same financial year, and both the first and last dates fall where they should, 1 April 2026 and 1 March 2027. The total of ₹2,75,25,000 is identical before and after.

Notice the check on the Total column before it is dropped. A Total that disagrees with its own months is a finding worth reporting: someone overtyped a month after the total was set, or the total is a formula over the wrong range. Deleting that column without looking would lose the only evidence.

The month arithmetic, spelled out

The formula (fy_month + 2) mod 12 + 1 maps 1 to 4 and 10 to 1. Keep the FY month number as its own column: sorting by it gives April-first order in any visual, and it makes fiscal quarters a simple integer division, as covered in adding Indian financial year and quarter columns.

Joining the result to actuals

Once both budget and actuals carry a first-of-month date, the comparison is a join on branch, head and month start, or a relationship to a shared date table in Power BI. Aggregate actuals to month level before joining; joining daily sales to a monthly budget repeats the budget on every day and multiplies it. The variance logic itself, including how to treat months that have not closed yet, is in comparing year-to-date spend and income against plan.

When the headers carry a year already

Some sheets label columns "Apr-26" to "Mar-27". That is easier, but only if the labels are text. If Excel has turned them into dates, the unpivoted Month column holds date serial numbers, and converting them needs the fallback described in reading dd/mm/yyyy dates in Power Query with en-IN locale. Either way, run the row-count and total checks after the reshape.

Reshaping wide planning sheets into tidy tables, and proving nothing was lost on the way, comes up in almost every reporting project in our Data Analyst course.