A pivot can fail because the proposed table has nowhere to put two different values for the same intersection. Removing the error without understanding those records can turn a visible problem into an incorrect report.

Work in a query copy with three fields: Item, Month and Units. Suppose it contains Item A, January, 2 and another Item A, January, 3. Decide whether these are two legitimate observations to add, a duplicated import, or records that need another identifier. The same visible pair can represent very different data problems.

Inspect the intersection before choosing an operation

In Power Query Editor, select the column whose values should become headings, then use Transform, Pivot Column. Choose the value column. Advanced options offers aggregation choices; the default attempts Sum. Don’t aggregate requires one value for each row-and-column intersection. Microsoft documents an error when multiple values meet that intersection instead.

For the example, Month supplies the future headings and Units supplies the values. If both source records represent distinct quantities that should contribute to the same monthly total, five is the expected summed result. If they are conflicting versions of one observation, five would hide the conflict rather than settle it.

Return to the unpivoted source view and inspect all fields for those records. Perhaps a Location field belongs in the row identity, or a record-version rule must select an authoritative entry before pivoting. Do not discard a field that distinguishes legitimate records merely to make the result look compact.

Check both a repeated and a unique case

Keep a second item-month pair that occurs once as a control. Verify its value alongside the repeated pair after applying the chosen rule. Then reconcile the source records that contributed to each result.

Record why an aggregation is meaningful, including the units and the observation represented by each source row. Counts, quantities and descriptive labels cannot all use the same reduction rule. Retain the source and the query steps so another reader can trace a pivoted number back to the observations that produced it.

Sources: Microsoft: pivot columns.