A new month disappeared when a finance table was unpivoted

A departmental schedule may store one month in each column, while the reporting system expects one row for each department and month. Unpivoting makes that transformation possible. When the next month is added, however, the query must still recognize the intended period columns. A refresh with no error can produce a table whose shape is valid but whose latest period is missing.

Inspect the transformation actually saved in the query. Microsoft distinguishes operations that unpivot a specified set of columns from operations that unpivot everything except a specified set. The difference determines how a newly added column is treated. Interface labels and generated steps deserve inspection; do not assume every command containing unpivot has the same future-column behavior.

Keep the original wide table and a recent approved output. Compare the distinct periods in the new output with the reporting calendar before examining a percentage variance. If April is absent from the transformed data, a zero April total may be a missing-column problem rather than evidence that no spending occurred.

List the fields that describe a row, such as department code, account, entity and scenario. Then list the fields representing amounts for periods. Preserve this distinction in the input specification. A new department-manager column is a descriptive field; a new month is a measurement field. Treating both as interchangeable additions can break either kind of unpivot rule.

With a specified-column operation, a new period may need to be included explicitly. With an all-other-columns operation, a newly added description can be pulled into the amount-and-period output unless it is deliberately excluded or otherwise handled. Neither arrangement is universally correct. Choose a rule that matches how the source is maintained and makes unexpected fields visible.

Agree how the query recognizes a period. A label such as Apr without a year can be ambiguous when the file contains more than one year. A date-shaped header may also be text that needs interpretation under a defined convention. Have the report owner establish the period meaning rather than letting a convenient automatic conversion decide it.

Use an invented table with two departments and three monthly amount columns. If every amount is present and each department appears once, the intended long-form result has six department-period rows. Adding a fourth complete month should create eight rows under that same scope. An output still containing only six merits investigation even if the original three months reconcile perfectly.

Suppose the first department has $100, $120, $130 and $150 across the four months, while the second has $200, $210, $220 and $230. The complete total is $1,360. A transformation that retains only the first three months reports $980, omitting $380 from the new month. The numbers are illustrative; the control is the reconciliation of each period and department to its source.

Include a new descriptive column in the test as well. A numeric cost-center label should not become an additional period amount simply because it can be converted to a number. The expected period list is a stronger check than asking only whether the transformed value column contains valid numeric data.

Determine what an empty source cell represents before deciding the expected row count. It might mean no plan submitted, a period outside scope or an amount deliberately left unknown. A numeric zero can instead be an explicit statement that the planned amount is zero. Those meanings should remain distinguishable when the table is reshaped.

Microsoft’s Table.Unpivot example shows null entries absent from its resulting attribute-value pairs. A simple department-count multiplied by month-count expectation therefore needs to account for the actual blank treatment. Do not fill every blank with zero merely to make the row count agree; that would change the information supplied by the business.

For a required complete planning grid, report missing submissions separately or create the required representation under an approved rule. Keep the original status available so a reviewer can tell which zeros were supplied and which values remain unconfirmed. The final report should not turn an absent budget into an apparently approved zero allocation.

Update the column-selection rule in a controlled copy, then compare the complete intended population. Check that identifiers repeat correctly on the new period rows and that amounts retain their signs and precision. If the output feeds a journal upload, forecast or dashboard, verify the fields expected by that destination after the change.

Review later steps that filter or type the generated period column. A corrected unpivot can produce April rows that a later fixed filter immediately removes. Likewise, a renamed attribute column can break a downstream reference. Follow the new period through to the final result rather than stopping at the first preview where it appears.

Check for an annual total column in the source. If it is unpivoted alongside its monthly components and then summed, the report can double count the same plan. Treat totals as control values or a separately defined measure when appropriate. Retain the reconciliation rather than allowing a convenient all-other-columns rule to choose their meaning.

Provide the source owner with a short format agreement covering row identifiers, period labels, descriptive fields, total columns and blank values. Ask for notice when that structure changes. A finance schedule can remain easy for departments to maintain without leaving the transformation dependent on an undocumented arrangement.

Keep a focused example containing a new period, a new description, a blank and a zero. Use it when changing the query or source template. Save the accepted period list and control totals with each release, so a later reviewer can establish that the reshaped table contains the months management intended to plan or report.