Power BI guide: managing the current views & a simple pivot pattern

by

This note explains how to turn the current views (wide–from–tall) into a report‑friendly table using Power Query (recommended) or DAX. It covers two common layouts:

  1. Attribute-only column headers
  2. META_TYPE + ATTRIBUTE as prefixed column headers

It also shows how to extract a human‑readable Description from the JSON in VALUE_METADATA.

Source schema (tall, meta-driven)

Expected columns:

  • OBJECT_SEQ
  • META_TYPE
  • ATTRIBUTE
  • CODE_VALUE
  • VALUE_METADATA (JSON)
  • CREATE_DATE
  • EXPIRY_DATE
  • SOURCE_SYSTEM
  • ACTIVE_FLAG
  • CHECKSUM

Assumption: Each (OBJECT_SEQ, [META_TYPE], ATTRIBUTE) appears at most once for the snapshot you are pivoting. If you have duplicates (e.g. overlapping validity), filter first (e.g. ACTIVE_FLAG = 1, or latest CREATE_DATE).

Pivot Pattern A — Attribute‑only headers

Goal: One row per OBJECT_SEQ, one column per ATTRIBUTE, with the cell value = CODE_VALUE.

Steps (Power Query)

  1. Start with your table in Power Query (Transform Data).
  2. Remove unneeded columns:
    • VALUE_METADATA, CREATE_DATE, EXPIRY_DATE, SOURCE_SYSTEM, ACTIVE_FLAG, CHECKSUM.
  3. Ensure key columns present: keep OBJECT_SEQ, ATTRIBUTE, CODE_VALUE (and optionally META_TYPE if needed for later checks).
  4. Pivot: Transform ➜ Pivot Column
    • Column to pivot: ATTRIBUTE
    • Values column: CODE_VALUE
    • Advanced options ➜ Aggregate value function: Don’t Aggregate.
  5. Result: a wide table with one column per attribute.

Notes

  • If Power Query forces an aggregation, you have duplicate (OBJECT_SEQ, ATTRIBUTE) rows. Resolve by filtering to one row per pair.

Pivot Pattern B — META_TYPE prefix + ATTRIBUTE

Goal: Use META_TYPE as a prefix so headers look like TYPE_ATTRIBUTE (e.g. EMP_Name).

Steps (Power Query)

  1. Add a new column that combines META_TYPE and ATTRIBUTE using the Text.Combine() function:
    • Add Column ➜ Custom Column
    • Name: ATTR_KEY
    • Formula (M): Text.Combine({[META_TYPE], [ATTRIBUTE]}, "_")
    Text.Combine() automatically ignores nulls, so you won’t get stray underscores when META_TYPE is blank or null.
  2. (Optional) Remove the original META_TYPE column after creating ATTR_KEY.
  3. Remove unneeded columns as in Pattern A.
  4. Pivot:
    • Column to pivot: ATTR_KEY
    • Values column: CODE_VALUE
    • Aggregate: Don’t Aggregate.

Adding a human‑readable Description

If you want a Description alongside or instead of CODE_VALUE, many attributes include JSON in VALUE_METADATA, typically with fields like CODE and DESC.

Steps (Power Query)

  1. Keep VALUE_METADATA for this step.
  2. Parse JSON: select VALUE_METADATA ➜ Transform ➜ Parse ➜ JSON. The column becomes Record values.
  3. Expand the record: click the expand icon and select the fields you need (usually CODE, DESC). You’ll get new columns, e.g. VALUE_METADATA.CODE, VALUE_METADATA.DESC.
  4. Create a final description column that uses DESC when available, otherwise falls back to CODE (or CODE_VALUE):
    • Add Column ➜ Custom Column
    • Name: VALUE_DESC
    • Formula (M): let d = [VALUE_METADATA.DESC], c = [VALUE_METADATA.CODE] in if d <> null and d <> "" then d else if c <> null and c <> "" then c else [CODE_VALUE]
  5. Clean up: remove intermediary columns you no longer need (e.g. VALUE_METADATA, VALUE_METADATA.CODE, VALUE_METADATA.DESC).
  6. Pivot on ATTRIBUTE (Pattern A) or ATTR_KEY (Pattern B) using VALUE_DESC as the Values column (and Don’t Aggregate).

Optional: Doing it in DAX instead of Power Query

Power Query is typically simpler for this pattern. If you must pivot in DAX (e.g. to keep the tall table and compute a wide view), create calculated columns or measures:

  • Prefixed header key (calculated column): ATTR_KEY = COALESCE([META_TYPE] & "_", "") & [ATTRIBUTE]
  • Wide table via summarise + pivot (pattern): Wide = VAR T = SELECTCOLUMNS('Tall', "OBJECT_SEQ", 'Tall'[OBJECT_SEQ], "ATTR_KEY", COALESCE('Tall'[META_TYPE] & "_", "") & 'Tall'[ATTRIBUTE], "VAL", 'Tall'[CODE_VALUE] ) RETURN SUMMARIZECOLUMNS( 'Tall'[OBJECT_SEQ], // One column per attribute key // Replace ATTR1, ATTR2 with actual attribute keys from your data model "ATTR1", CALCULATE(SELECTEDVALUE(T[VAL])), "ATTR2", CALCULATE(SELECTEDVALUE(T[VAL])) ) This is more manual and brittle than Power Query. Prefer Power Query where possible.

End‑to‑end example (Power Query M)

Below is a compact M script you can adapt. It implements Pattern B with Description extraction and falls back to CODE_VALUE when JSON is missing.

let
  Source = YourSourceHere,
  KeepCols = Table.SelectColumns(Source, {"OBJECT_SEQ","META_TYPE","ATTRIBUTE","CODE_VALUE","VALUE_METADATA","ACTIVE_FLAG","CREATE_DATE"}, MissingField.Ignore),
  AddKey = Table.AddColumn(KeepCols, "ATTR_KEY", each Text.Combine({[META_TYPE], [ATTRIBUTE]}, "_"), type text),
  ParseJSON = Table.TransformColumns(AddKey, {{"VALUE_METADATA", each try Json.Document(_) otherwise null}}),
  ExpandJSON = Table.ExpandRecordColumn(ParseJSON, "VALUE_METADATA", {"CODE","DESC"}, {"META_CODE","META_DESC"}),
  AddDesc = Table.AddColumn(ExpandJSON, "VALUE_DESC", each let d=[META_DESC], c=[META_CODE] in if d <> null and d <> "" then d else if c <> null and c <> "" then c else [CODE_VALUE], type text),
  Trim = Table.SelectColumns(AddDesc, {"OBJECT_SEQ","ATTR_KEY","ATTRIBUTE","CODE_VALUE","VALUE_DESC"}),
  ValuesColumn = "CODE_VALUE", // or switch to "VALUE_DESC"
  Pivoted = Table.Pivot(Trim, List.Distinct(Trim[ATTR_KEY]), "ATTR_KEY", ValuesColumn)
in
  Pivoted

Don’t Aggregate in the UI corresponds to Table.Pivot without an aggregation function in M. If you see an aggregation function (e.g. List.Sum), you have duplicates — fix upstream.

Quality & performance tips

  • De‑duplicate before pivot to guarantee one row per (OBJECT_SEQ, header key).
  • Filter to current rows if your source contains history. Typical filters:
    • ACTIVE_FLAG = 1, or
    • EXPIRY_DATE is null / in the future, or
    • keep the latest CREATE_DATE per key.
  • Column data types: set CODE_VALUE/VALUE_DESC to Text before pivot to avoid unintended numeric aggregation.
  • Column naming hygiene: prefer TYPE_ATTR without spaces to simplify DAX.
  • Downstream joins: keep OBJECT_SEQ as the key to join to your fact/dimension tables.

Quick checklist

  • Removed: VALUE_METADATA, CREATE_DATE, EXPIRY_DATE, SOURCE_SYSTEM, ACTIVE_FLAG, CHECKSUM (unless needed for filtering).
  • Built header key: ATTRIBUTE or META_TYPE + _ + ATTRIBUTE.
  • JSON parsed and VALUE_DESC created (optional).
  • Pivoted on header key with Don’t Aggregate and Values = CODE_VALUE or VALUE_DESC.
  • Duplicates resolved prior to pivot.
  • Data types set correctly.

FAQ

Q: Is there a COALESCE() in Power Query?
A: No. Use Text.Combine() instead, which naturally ignores nulls. COALESCE() does exist in DAX.

Q: Why am I forced to aggregate in the pivot dialog?
A: You have duplicate (OBJECT_SEQ, HeaderKey) rows. Filter to a single current row per key before pivoting.

Q: Where do I get descriptions?
A: From VALUE_METADATA JSON → expand to CODE/DESC → build VALUE_DESC with fallbacks → pivot using VALUE_DESC if desired.

Leave a Comment