[Power BI] Costs query fails with type mismatch when x_SkuTerm contains ISO 8601 duration strings (P1Y, P3Y, P5Y)

[bpurchell]

🐛 Problem

Cost Management FOCUS exports have begun emitting x_SkuTerm as ISO 8601 duration strings (e.g. P3Y) instead of integers (e.g. 36). This is a breaking change that causes Power BI storage report refreshes to fail with a type mismatch error when the Costs query attempts to load data.

The following system error occurred: Type mismatch. Unable to convert a value to the data type requested
for table 'Costs' column 'x_SkuTerm'. The data provider was unable to convert the value '' from the
source data type 'VT_BSTR' to the expected data type 'VT_I8'. Please check the column data types in the
data model and data source and ensure that the data types are compatible.

The root cause is two-fold:

1. ISO 8601 duration strings — reservation rows now emit P1Y, P3Y, P5Y instead of 12, 36, 60. The existing OptionalFixes step handles the old 1Year/3Years/5Years legacy strings but not the new ISO 8601 format.
2. Empty string instead of null — on-demand rows (the majority of rows for most accounts) emit "" for x_SkuTerm rather than null. Once the semantic model enforces VT_I8 on the column due to a string value like P3Y being present, the empty string rows also fail.

Note: A similar fix was already applied to the pricesheet query for P10Y in April — this issue covers the same pattern in the Costs query.

👣 Repro steps

1. Configure a Cost Management FOCUS export (any version — 1.0, 1.0r2, or 1.2-preview)
2. Ensure the billing account has at least one active reservation
3. Connect a FinOps toolkit Power BI storage report to the exported data
4. Refresh the report
5. See error in the Costs query: Unable to convert a value to the data type requested for column 'x_SkuTerm'

🤔 Expected

• ISO 8601 x_SkuTerm values (P1Y, P3Y, P5Y, P10Y) should be normalized to integer months (12, 36, 60, 120), consistent with the existing handling of 1Year/3Years/5Years
• Empty string x_SkuTerm values should be coerced to null
• x_SourceChanges should flag ISO 8601 values as SkuTermShouldBeAnInteger for auditability
• Power BI storage report refreshes should complete successfully

📷 Screenshots

Power BI refresh failure showing the type mismatch on x_SkuTerm:

Unable to convert a value to the data type requested for table 'Costs' column 'x_SkuTerm'. The data provider was unable to convert the value '' from the source data type 'VT_BSTR' to the expected data type 'VT_I8'.

🔧 Environment

• FinOps hub version: Storage-based reports (no hub)
• Billing account type: EA (Enterprise Agreement)
• Power BI report type: Storage-based
• Cost Management export: FOCUS 1.0 (also reproducible on 1.0r2 and 1.2-preview — format change occurs at the export level regardless of FOCUS version)

ℹ️ Additional context

Microsoft has silently changed the x_SkuTerm column format in Cost Management FOCUS exports. The column previously held integer values or the legacy strings already handled by OptionalFixes. The new ISO 8601 format appears to have been introduced around June 2026.

The OptionalFixes step in the Costs query currently handles:

"1Year",  12, Replacer.ReplaceValue, {"x_SkuTerm"}),
"3Years", 36, Replacer.ReplaceValue, {"x_SkuTerm"}),
"5Years", 60, Replacer.ReplaceValue, {"x_SkuTerm"}),

It does not handle P1Y, P3Y, P5Y, P10Y, or "". A fix has been submitted via pull request.

🙋‍♀️ Ask for the community

We could use your help:

1. Please vote this issue up (👍) to prioritize it.
2. Comment if you are seeing P1Y, P3Y, P5Y, or other ISO 8601 values in your x_SkuTerm column to help confirm how widespread the export format change is.
3. Comment with your FOCUS export version and billing account type to help identify if this affects specific account configurations.

[microsoft-github-policy-service]

Thank you for submitting this issue! We try to respond within 3 business days. In the meantime:

• 📖 Check the troubleshooting guide for common issues
• 💬 Ask questions in Discussions
• 📅 Join our biweekly office hours for live help from the team

[RolandKrummenacher]

Duplicate of #2158