BusOpp: Meta_Codes

by

What this view is for

Busopp.meta_codes provides the categorisation and contextual labels for business opportunities.

If core_details tells you what the opportunity is, meta_codes tells you how it should be grouped, classified or interpreted.

This is where things like type, category, region, ownership and other descriptive attributes live.

Level of detail (grain)

  • Multiple rows per opportunity
  • One row per code attribute

This view is intentionally long and thin.
An opportunity with ten code attributes will appear ten times — once for each attribute.

What you’ll find in this view

Each row represents a single coded attribute for an opportunity, typically including:

  • OBJECT_SEQ (the opportunity identifier, paired with company)
  • META_TYPE (the category of the code)
  • ATTRIBUTE (the specific attribute name)
  • A value payload (often delivered as JSON, such as code + description)

New attributes can be added over time without changing the structure.

How to join this view

Every row includes the same OBJECT_SEQ used in Busopp.core_details.

Always join Busopp.meta_codes to Busopp.core_details using OBJECT_SEQ.

This ensures attributes stay aligned to the correct opportunity and avoids accidental cross-company joins.

How this view is commonly used

meta_codes is typically used to:

  • Slice and filter opportunities (e.g. by region, market, route-to-market)
  • Group pipeline values meaningfully
  • Provide labels and categories for visuals
  • Keep your reporting modular (codes here, dates in meta_dates, values in item_values_lines)

It’s rarely aggregated directly — it provides context for values and dates.

Working with the long, thin structure

Because this view is long and thin, it’s usually shaped in the report layer.

A common approach is:

  1. Filter to the code types you need
  2. Create a combined attribute identifier
  3. Pivot to a wide, report-friendly shape

Example (Power Query):

= Table.AddColumn(
    Source,
    "CODE_ATTRIBUTE",
    each [META_TYPE] & "_" & [ATTRIBUTE],
    type text
)

Once this column exists, you can pivot on CODE_ATTRIBUTE to produce a wide attributes table with one row per opportunity.

Codes Helper for slicers

For slicers, it’s often cleaner to build a small Codes Helper table rather than slicing directly from the long-thin meta_codes table in the model.

A helper table:

  • gives you a distinct list of available codes + descriptions
  • avoids bringing opportunity grain into slicers
  • reduces the risk of odd filtering behaviour
  • is a tidy input to slicers across the report

Here’s a useful pattern that:

  • parses the JSON payload
  • expands code/description
  • selects only the relevant fields
  • groups to distinct combinations of META_TYPE, ATTRIBUTE, CODE, DESC
let
    Source = src_Meta_Codes,
    #"Parsed JSON" = Table.TransformColumns(Source,{{"VALUE_METADATA", Json.Document}}),
    #"Expanded VALUE_METADATA" = Table.ExpandRecordColumn(#"Parsed JSON", "VALUE_METADATA", {"CODE", "DESC"}, {"CODE", "DESC"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded VALUE_METADATA",{"CODE", "DESC",  "META_TYPE", "ATTRIBUTE"}),
    #"Grouped Rows" = Table.Group(#"Removed Other Columns", {"META_TYPE", "ATTRIBUTE", "CODE", "DESC"}, {{"Count", each Table.RowCount(_), Int64.Type}})
in
    #"Grouped Rows"

Tip: that Count is handy during development (it shows whether a code is actually appearing), and you can drop it later if you want a pure dimension table.

Codes currently available in Busopp meta_codes

These are the available META_TYPE / ATTRIBUTE pairs:

META_TYPEATTRIBUTE
PROGRAMMESTRATEGIC_PILLAR
PROGRAMMEDELIVERY
PROGRAMMEBUSINESS
LINK_KEYPRU_CODE
LINK_KEYCUSTOMER_ID
LINK_KEYPROJECT_ID
OPPORTUNITYRISK_LEVEL
OPPORTUNITYPROBABILITY
OPPORTUNITYROUTE_TO_MARKET
SALESBUSINESS_TYPE
SALESREGION
SALESDISTRICT
SALESMARKET
SALESTARGET_CUSTOMER
SALESLEAD_SOURCE
STATUSOPERATIONAL
STATUSCLOSED_OUTCOME
STATUSWON_LOST_REASON
STATUSLIFECYCLE_STAGE
CUSTOMERREPRESENTATIVE
CUSTOMERCOMPANY
CUSTOMERCONTACT
CUSTOMERINTERMEDIATE_CUSTOMER

Things to watch out for

  • Don’t assume one row equals one opportunity — always consider grain
  • Anchor joins through core_details using OBJECT_SEQ
  • Be deliberate about which attributes you pivot (avoid pivoting “everything” unless you truly need it)

A quick sense-check: if counts jump unexpectedly after adding meta_codes, revisit joins and grain first.

Key takeaway

Busopp.meta_codes is your flexible, extensible catalogue of opportunity attributes.

Use it to categorise, slice and label — and if you want clean slicers, build a Codes Helper from distinct META_TYPE / ATTRIBUTE / CODE / DESC combinations.

Leave a Comment