Skill Standardization Pipeline

Dataset context

The imported CSV dataset is the output of a Python script that extracts highlighted skill mentions from annotated job posting excerpts.

The script processes structured Word documents with job posting metadata and posting excerpts with highlighted skill mentions, where highlight color encodes the observed skill type (hard or soft).

Extracted skill mentions are written to a CSV file with the fields: job_id, extracted_skill, observed_skill_type, where each row represents a single observed skill occurrence.

Figure 2. Example job posting excerpt with highlighted skill mentions.

Input schema

Each raw skill mention is defined by the following fields:

• • job_id: unique job posting identifier
• • extracted_skill: exact skill phrase used
• • observed_skill_type: skill type observed during annotation (hard or soft). Used for preliminary grouping and filtering. Final classification is determined by the skill_type taxonomy field. This separates initial observations from authoritative classification decisions.

Figure 3. Example rows from the imported raw skill mentions dataset.

Definition

An incoming raw skill is any row of the imported dataset that is not present in the raw-to-preliminary mapping table.

Incoming raw skills can occur in two situations:

• • when rows are added to the imported CSV file as new job postings get processed upstream
• • when a mapping is modified in the raw-to-preliminary mapping table so that it no longer matches any existing raw skill mentions

Implementation

The queue is implemented in Power Query via a left anti join on the raw skill mentions dataset and the raw-to-preliminary mapping table. The join uses a composite key of job_id, extracted_skill, and observed_skill_type, reflecting the raw skill mention schema defined by the input dataset.

Figure 4. Empty incoming raw skills queue.

Queue status indicator

The refresh-based queue design introduces two operational limitations, which are addressed by a real-time queue status indicator located above the mapping table:

• •

  Pipeline progress monitoring.

  Since the queue and corresponding mapping table are located on separate sheets, monitoring mapping completeness requires manual sheet navigation.

  The queue status indicator displays the count of unmapped raw skills directly above the mapping table, to quantify standardization progress.

• •

  Instant validation feedback.

  If an already mapped raw skill is modified within the mapping table, the queue does not include the affected raw skill mention until the next refresh, causing a temporary discrepancy between perceived and true mapping completeness.

  The queue status indicator provides instant feedback if an already mapped raw skill is modified and no longer matches the source data.

Figure 6. Queue status indicator updating as an already mapped raw skill gets modified.

The queue status indicator is implemented as a dynamic formula that filters the imported raw skill mentions dataset for rows without a match in the raw-to-preliminary mapping table. The formula counts the filtered rows and outputs the number of incoming raw skills above the mapping table.

// Queue status indicator formula
=LET(
   incoming_skills_count,

   ROWS(
      FILTER(
         raw_skills_import[#Data],
         BYROW(
            raw_skills_import[#Data],           
            LAMBDA(
               row,           
               NOT(
                  ISNUMBER(
                     XMATCH(
                        1,
                        (RawToPreliminarySkills[job_id] = INDEX(row, 1)) *
                        (RawToPreliminarySkills[extracted_skill] = INDEX(row, 2)) *
                        (RawToPreliminarySkills[observed_skill_type] = INDEX(row, 3))
         ))))), 
         NA()
      )
   ),

   "Incoming raw skills: " &
   IFNA(incoming_skills_count, "0 ✓")
)

Preliminary standardization

For each imported raw skill mention, the user enters a preliminary standardization, e.g.:

• • “Support multiple functions” → Cross-Functional Collaboration
• • “Create relevant Documentation” → Technical Documentation
• • “Ensure data accuracy and integrity” → Data Integrity Control

This phase aims to eliminate surface-level variation across skill mentions, serving as an initial vocabulary normalization layer.

Since preliminary skills are meant to stay close to the original phrasing, they retain semantic granularity. The second standardization phase then consolidates synonymous preliminary skills, producing a more analytically meaningful final dataset.

Mapping workflow

The mapping workflow starts by copying incoming raw skill mentions from the queue into the mapping table. This human-in-the-loop workflow helps prevent accidental overwrites that may result from automatic row insertion.

All original job_id, extracted_skill, and observed_skill_type values get copied into the mapping table. As a result, each skill mapping contains a complete source record and maintains a traceable data lineage.

Once a raw skill mention is copied into the mapping table, the user enters a preliminary_skill. The goal is to identify the core competency within the extracted phrase, while preserving contextual nuance.

Figure 7. Example of diversely phrased skill mentions mapping to a single preliminary skill.

Standardized skill lookup

The resulting preliminary skills are later used in the second mapping phase, where each unique preliminary_skill is assigned a taxonomy-aligned standardized_skill.

The raw-to-preliminary mapping table automatically fetches the final standardizations using XLOOKUP against the preliminary-to-standardized mapping table.

This lookup architecture achieves:

• •

  Centralized standardization logic. A unique preliminary skill is standardized only once, and the standardization applies to every instance of the preliminary skill.

• •

  Mapping drift prevention. The final standardized skill is derived rather than manually entered, which protects against inconsistent preliminary → standardized mappings.

• •

  Full pipeline visibility. The lookup keeps the full raw → preliminary → standardized skill transformation visible from a single table.

Validation behavior

The mapping table contains two validation columns, producing row-level data integrity metadata:

• • valid_mapping: contains Boolean flags indicating mapping success or failure
• • error_message: displays the exact issue(s) causing a mapping failure

The mapping integrity validation is implemented as follows:

// Formula in valid_mapping column
=AND(
   IS_VALID_RAW_SKILL([@[job_id]], [@[extracted_skill]], [@[observed_skill_type]]), 
   IS_UNIQUE_RAW_SKILL([@[job_id]], [@[extracted_skill]], [@[observed_skill_type]]),    
   NOT(IS_EMPTY([@[preliminary_skill]])),    
   IS_MAPPED_PRELIMINARY_SKILL([@[preliminary_skill]]),    
   AND(
      ISFORMULA([@[standardized_skill]]), 
      [@[standardized_skill]] = EXPECTED_STANDARDIZED_SKILL_LOOKUP_RESULT([@[preliminary_skill]])
   )
)


// IS_VALID_RAW_SKILL
=LAMBDA(
   job_id, extracted_skill, observed_skill_type,

   ISNUMBER(
      XMATCH(
         1,
         (raw_skills_import[job_id] = job_id) *
         (raw_skills_import[extracted_skill] = extracted_skill) *
         (raw_skills_import[observed_skill_type] = observed_skill_type)
      )
   )
)


// IS_UNIQUE_RAW_SKILL
=LAMBDA(
   job_id, extracted_skill, observed_skill_type,

   AND(
      IS_VALID_RAW_SKILL(
         job_id, extracted_skill, observed_skill_type
      ),
      COUNTIFS(
         RawToPreliminarySkills[job_id], job_id,
         RawToPreliminarySkills[extracted_skill], extracted_skill,
         RawToPreliminarySkills[observed_skill_type], observed_skill_type
      ) = 1
   )
)


// IS_EMPTY
=LAMBDA(
   cell,
    
   LEN(
      TRIM(
         CLEAN(
            // replace non-breaking space with standard space
            SUBSTITUTE(
               cell, CHAR(160), " "
      )))
   ) = 0
)


// IS_MAPPED_PRELIMINARY_SKILL
=LAMBDA(
   preliminary_skill,

   IFNA(
      XLOOKUP(
         preliminary_skill,
         PreliminaryToStandardSkills[preliminary_skill],
         PreliminaryToStandardSkills[valid_mapping]
      ),
      FALSE
   )
) 


// EXPECTED_STANDARDIZED_SKILL_LOOKUP_RESULT
=LAMBDA(
   prelim_skill,
   IF(
      XLOOKUP(
         prelim_skill,
         PreliminaryToStandardSkills[preliminary_skill],
         PreliminaryToStandardSkills[valid_mapping],
         ""
      ) <> TRUE,  // fetch only valid standardized skill mappings
      "",
      XLOOKUP(
         prelim_skill,
         PreliminaryToStandardSkills[preliminary_skill],
         PreliminaryToStandardSkills[standardized_skill],
         ""
      )
   ) & ""
)

Definition

A pending preliminary skill is a preliminary_skill in the raw-to-preliminary mapping table that is not present in the preliminary-to-standardized mapping table.

This occurs when a raw skill mention is sufficiently different from other raw skills to require a new preliminary skill standardization.

Figure 8. Demo of pending preliminary skills queue showing different queue states and interactivity with the corresponding mapping table.

Implementation

The queue is implemented as a dynamic array formula using FILTER on the raw-to-preliminary mapping table for non-empty preliminary_skill values that have no match in the preliminary-to-standardized mapping table.

When pending preliminary skills are present, the formula displays them as a sorted, unique list. Alternatively, the formula returns “(none)” to signal an empty queue state.

The queue is located on the same worksheet as the preliminary-to-standardized mapping table, to support standardization progress monitoring. As preliminary skills are added from the queue into the mapping table, the queue provides instant feedback by shrinking in size and updating its counter.

// Pending preliminary skills queue formula
=IFNA(
   SORT(
      UNIQUE(
         FILTER(
            RawToPreliminarySkills[preliminary_skill],
                
            NOT(
               IS_EMPTY(
                  RawToPreliminarySkills[preliminary_skill]
               )
            ) *
            NOT(
               ISNUMBER(
                  XMATCH(
                     RawToPreliminarySkills[preliminary_skill],
                     PreliminaryToStandardSkills[preliminary_skill]
                  )
               )
            ),

            NA()
   ))),

   "(none)"
)


// IS_EMPTY
=LAMBDA(
   cell,
    
   LEN(
      TRIM(
         CLEAN(
            // replace non-breaking space with standard space
            SUBSTITUTE(
               cell, CHAR(160), " "
      )))
   ) = 0
)

Taxonomy-aligned standardization

For each unique preliminary skill entered in the first stage, the user selects a taxonomy-aligned final standardization in the second stage. Selection is performed through a validated drop-down interface populated from a controlled skill taxonomy.

This mapping architecture achieves:

• •

  Final dataset consistency. The selection-based design ensures that only preapproved standardized skills enter the final dataset, as opposed to allowing ad-hoc standardization.

• •

  Consolidation of synonymous skills. Multiple preliminary skills can map to a single standardized skill, which helps create a more analytically meaningful final dataset.

• •

  Centralized and propagated updates. Updating a final standardization only requires changing a single row in the preliminary-to-standardized mapping table. The updated standardized skill is then automatically propagated to all related raw skill mentions through the preliminary mapping layer.

Figure 9. Example of diverse raw skill mentions mapping to a smaller number of preliminary skills that converge into a single standardized skill.

Mapping workflow with optional taxonomy expansion

The workflow begins by copying pending preliminary skills from the queue into the mapping table. Then, for each preliminary_skill, the user selects a standardized_skill from a drop-down interface.

There are two possible workflows:

1. Select preexisting standardized skill.

   This happens when a preliminary skill is synonymous with an already existing standardized skill and introducing a separate standardization would risk fragmenting the final dataset.

2. Expand skill taxonomy prior to selection.

   This occurs when a preliminary skill corresponds to a tool, technology, or competence that is not yet present in the skill taxonomy.

   In this case, the user first expands the taxonomy with the new standardized skill and its classification (at least the skill type: hard or soft). The new entry is automatically added to the mapping table’s drop-down menu, which the user can now select.

Figure 10. Demo of preliminary to standardized skill mapping workflow with taxonomy-aligned selection.

Validation behavior

The mapping table contains two validation columns, producing row-level data integrity metadata:

• • valid_mapping: contains Boolean flags indicating mapping success or failure
• • error_message: displays the exact issue(s) causing a mapping failure

The mapping integrity validation is implemented as follows:

// Formula in valid_mapping column
=AND(
   NOT(IS_EMPTY([@[preliminary_skill]])),
   ISNUMBER(
      XMATCH(
         [@[preliminary_skill]],
         RawToPreliminarySkills[preliminary_skill]
   )),
   // uniqueness validation via cleaned helper column
   COUNTIF(
      [cleaned_preliminary_skill], 
      [@[cleaned_preliminary_skill]]
   ) = 1,
   NOT(IS_EMPTY([@[standardized_skill]])),
   IS_VALID_STANDARDIZED_SKILL([@[standardized_skill]])
)


// IS_EMPTY
=LAMBDA(
   cell,
    
   LEN(
      TRIM(
         CLEAN(
            // replace non-breaking space with standard space
            SUBSTITUTE(
               cell, CHAR(160), " "
      )))
   ) = 0
)


// IS_VALID_STANDARDIZED_SKILL
=LAMBDA(
   standard_skill,

   LET(
      is_valid,
      
      XLOOKUP(
         standard_skill,
         SkillTaxonomy[standardized_skill],
         (SkillTaxonomy[valid_standardized_skill] * SkillTaxonomy[valid_classification]) = 1
      ),

      IFNA(
         is_valid,
         FALSE
      )
   )
) 

Definition

Each row of the skill taxonomy table represents a unique standardized skill, classified by skill type (hard or soft), skill category, and subcategory. These entries form the authoritative list of standardized skills that the imported raw skill mentions can ultimately be mapped into.

The taxonomy serves three primary functions within the standardization pipeline:

• •

  Standardization governance. Only taxonomy-approved skills can enter the final standardized dataset.

• •

  Consolidation of skill variants. Standardized entries reduce surface-level variation between raw skill mentions, ensuring reliable aggregations and enabling normalized analysis of skill demand across job postings.

• •

  Analytical classification. Each standardized skill is enriched with classification metadata that supports dimensional analysis across skill types, categories, and subcategories.

Taxonomy expansion workflow

As new job postings are processed upstream, the standardization pipeline receives additional raw skill mentions.

When a job posting phrase corresponds to a tool, technology, or competence not yet present in the skill taxonomy, the taxonomy must be expanded with a new standardized skill before the raw skill mention can receive a final standardization.

The taxonomy can be expanded in two steps:

1. Enter standardized skill. The user creates a new taxonomy entry representing the tool, technology, or competence that requires standardization.

2. Select skill classification. The user performs a step-by-step selection of skill type (hard or soft) → skill category → skill subcategory. Only pre-approved classifications are permitted, implemented via a cascading drop-down validation system.

If the new taxonomy entry is valid – as determined by the valid_standardized_skill and valid_classification fields – it becomes automatically available in the second-stage mapping table as a final standardization option.

Figure 11. Demo of expanding the taxonomy with a new standardized skill using hierarchical classification.

Skill classification lookup table

Pre-approved skill classifications – unique skill type, category, and subcategory combinations – are curated in a dedicated lookup table.

This table directly impacts downstream skill demand analysis since it defines the analytical categories that related skills are grouped by.

Figure 12. Reference table of approved skill type, category, and subcategory combinations.

Cascading drop-down classification

A skill type selection (hard or soft) filters the available skill categories, while the selected category limits the available skill subcategory options.

This protects against erroneous categorizations such as Soft skill → Interpersonal skills → Statistical methods.

The cascading drop-down validation is implemented using hidden helper sheets that dynamically generate the possible skill category and subcategory options for each taxonomy row. The category_validation sheet filters the classification lookup table according to the selected skill type, while the subcategory_validation sheet filters available subcategories based on the selected skill category.

// Spill-based data validation list
=INDEX(category_validation!$A:$A, ROW())#


// Formula in category_validation sheet 
=TRANSPOSE(
   SORT(
      UNIQUE(
         FILTER(
            SkillClassifications[skill_category], 

            TRIM(
               CLEAN(
                  SkillClassifications[skill_type]
            )) = 
            TRIM(
               CLEAN(
                  SkillClassifications[@[skill_type]]
            )),

            NA()
))))

Validation behavior

The skill taxonomy table contains three validation columns, producing row-level data integrity metadata:

• • valid_standardized_skill: contains Boolean flags indicating standardized skill validity.
• • valid_classification: contains Boolean flags indicating skill classification validity. Requires a valid standardized skill as a classification target.
• • error_message: displays the exact issue(s) associated with a taxonomy entry.

The taxonomy integrity validation is implemented as follows:

// Formula in valid_standardized_skill column
=AND(
   NOT(IS_EMPTY([@[standardized_skill]])),
   COUNTIF(
      [cleaned_standardized_skill], 
      [@[cleaned_standardized_skill]]
    ) = 1
)


// Formula in valid_classification column
=AND(
   [@[valid_standardized_skill]],
   IS_VALID_SKILL_CLASSIFICATION(
      [@[skill_type]], 
      [@[skill_category]], 
      [@[skill_subcategory]]
   )
)


// IS_VALID_SKILL_CLASSIFICATION
=LAMBDA(
   skill_type,
   skill_category,
   skill_subcategory,

   ISNUMBER(
      XMATCH(
         1,
         (SkillClassifications[skill_type] = skill_type) *
         (SkillClassifications[skill_category] = skill_category) *
         (SkillClassifications[skill_subcategory] = skill_subcategory)
      )
   )
)

Pipeline Overview

• •

  Measures pipeline scale. Total imported skill mentions and job postings processed.

• •

  Quantifies skill consolidation. Raw → preliminary → standardized skill counts.

• •

  Monitors mapping validity. Valid versus total skill mappings.

• •

  Supports filtered analysis. Comparison of filtered and full dataset metrics.

The filtered context metric generation is implemented as follows:

// Total standardized skills in filtered context
=IFNA(
   ROWS(
      UNIQUE(
         FILTER(
            RawToPreliminarySkills[standardized_skill],

            (LEN(
               RawToPreliminarySkills[standardized_skill]
            ) > 0) *
            (BYROW(
               RawToPreliminarySkills[standardized_skill],

               LAMBDA(row,
                  SUBTOTAL(
                     103, // COUNTA, ignoring hidden rows 
                     row
                  )
               )
            )),

            NA()
   ))),

   0
)

Mapping Diagnostics

• •

  Aggregates validation errors by type:

  • ▪ Unknown raw skills
  • ▪ Duplicate raw skills
  • ▪ Empty preliminary skills
  • ▪ Unmapped preliminary skills (unique)
  • ▪ Overwritten standardization lookup
• •

  Summarizes remaining mapping work. Number of raw skills missing preliminary standardization, and number of unique preliminary skills requiring final standardization.

Figure 14. Mapping diagnostics panel updating automatically as validation errors are introduced into the raw-to-preliminary mapping table.

The mapping diagnostics panel is implemented as follows:

// Mapping diagnostics error reporting formula
=LET(
   CountErrorType,
   LAMBDA(
      error_type,
      SUMPRODUCT(
         --(BYROW(
               RawToPreliminarySkills[error_message],
               LAMBDA(
                  row,
                  ISNUMBER(
                     FIND(
                        error_type,
                        row
                     )
                  )
               )
         ))
      )
   ),
     
   UnknownRaw, CountErrorType("Unknown raw skill"),
   DuplicateRaw, CountErrorType("Duplicate raw skill"),
   EmptyPrelim, CountErrorType("Empty preliminary skill"),
   ManualOverride, CountErrorType("Overwritten standardization lookup"),

   UniqueUnmappedPrelim,
   IFNA(
      ROWS(
         UNIQUE(
            FILTER(
               RawToPreliminarySkills[preliminary_skill],
               ISNUMBER(
                  FIND(
                     "Unmapped preliminary skill",
                     RawToPreliminarySkills[error_message]
                  )
               ),
               NA()
      ))),
      0
   ),
   
   // Error display logic
   IF(
      COUNTIF(
         RawToPreliminarySkills[valid_mapping],
         FALSE
      ) = 0,
      
      " No errors detected ✓",

      TEXTJOIN(
         CHAR(10),  // errors separated by newline
         TRUE,      // show only existing errors

         IF(UnknownRaw > 0, "▪ Unknown raw skills: " & UnknownRaw, ""),
         IF(DuplicateRaw > 0, "▪ Duplicate raw skills: " & DuplicateRaw, ""),
         IF(EmptyPrelim > 0, "▪ Empty preliminary skills: " & EmptyPrelim, ""),
         IF(UniqueUnmappedPrelim > 0, "▪ Unmapped preliminary skills (unique): " & UniqueUnmappedPrelim, ""),
         IF(ManualOverride > 0, "▪ Overwritten standardization lookup: " & ManualOverride, "")
      )
   )
)

Output schema

Each row of the final dataset represents a single imported raw skill mention enriched with its final standardization and classification, defined by the following fields:

• • job_id: Unique job posting identifier
• • extracted_skill: Exact skill phrase extracted from the original job posting
• • standardized_skill: Standardized skill phrase
• • skill_type: Standardized skill type (hard or soft)
• • skill_category: Main skill category
• • skill_subcategory: Skill subcategory

The output dataset is implemented in Power Query via a left outer join between:

1. The raw-to-preliminary mapping table with derived standardized skills
2. The skill taxonomy table

using the shared standardized_skill field as the join key.

To ensure analytical integrity, validation fields are used to exclude invalid records from the final dataset. In order to be used for analysis, each record must satisfy the following requirements:

valid_mapping = TRUE
valid_standardized_skill = TRUE 
valid_classification = TRUE 

Interactive skill demand dashboard

The final dataset powers an interactive dashboard that enables multidimensional exploration of skill demand across the analyzed job postings.

Analytical capabilities include:

• • Skill prevalence analysis
• • Hard vs soft skill comparison
• • Skill category analysis
• • Skill subcategory analysis
• • Individual skill drilldown
• • Dynamic KPI reporting

Figure 15. Dashboard overview.