Pivot-me: Working with long, thin meta tables

by

The meta_codes, meta_dates (and any derived meta_values) views are intentionally long and thin.

This structure:

  • Keeps the model flexible
  • Allows new attributes to be added without breaking reports
  • Supports modular, metadata-driven reporting

That said, long thin tables aren’t always the easiest shape to work with directly in a reporting tool.

Shaping meta data into a report-friendly form

Once you’ve applied some basic filtering (for example, keeping only the code or date types you actually need), we strongly recommend a small amount of shaping in the report layer.

Step 1: Create a combined attribute field

First, create a derived column that combines META_TYPE and ATTRIBUTE.
This gives each row a clear, unique identity.

Example (Power Query):

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

This step:

  • Preserves the flexibility of the long thin model
  • Removes ambiguity between similarly named attributes
  • Makes the next step much simpler

Step 2: Pivot to a wide, recognisable shape

With the combined attribute in place, you can now pivot the table so that:

  • Each CODE_ATTRIBUTE becomes a column
  • Each object (via OBJECT_SEQ) becomes a single row
  • Attribute values become easy-to-use fields

The result is a wide, report-friendly attributes table that feels much more natural to work with in visuals and measures.

This is often the point where the data “clicks” for report writers.

When this approach works best

This pattern is especially useful when:

  • You know which attributes you need
  • The attribute set is relatively stable for the report
  • You want clean, readable fields without repeated filtering logic

If requirements change, you can always return to the long thin source and reshape again.

The takeaway

The long thin structure is there to give you flexibility.
Creating a combined attribute and pivoting it is how you turn that flexibility into something immediately usable.

Think of it as:

  • Long thin for storage and evolution
  • Wide and explicit for reporting and insight

Used together, they give you the best of both worlds.

Leave a Comment