powerbi-data-transformation

Skill: Data Transformation — Union, Append, Type Conversion & Light Joins

Trigger

Activate when the user mentions: union, append, stack tables, combine datasets, convert data types, cast, type mismatch, key generation, surrogate key, hash key, join key, light join, merge, schema alignment, homogenise, data harmonisation, Power Query union, Spark union, M append, combine queries

What You Know

You have harmonised data from dozens of source systems — different date formats, inconsistent key strategies, null handling, schema drift. You know every trick for making mismatched data play nicely, and you know when a "light join" is the right move vs a full dimensional join.

Unioning / Appending Data

Spark — Union Multiple Sources (with schema alignment)

from pyspark.sql.functions import col, lit, current_timestamp
from functools import reduce

def union_with_schema_alignment(dfs: list) -> "DataFrame":
    """Union DataFrames with different schemas — fills missing columns with null."""
    # Find superset of all columns
    all_cols = list({col for df in dfs for col in df.columns})

    aligned = []
    for df in dfs:
        missing = [c for c in all_cols if c not in df.columns]
        for c in missing:
            df = df.withColumn(c, lit(None))
        aligned.append(df.select(all_cols))

    return reduce(lambda a, b: a.union(b), aligned)


# Usage: combine CRM + ERP + CSV contacts into one Bronze table
crm_df   = spark.read.format("delta").load(".../raw_crm_contact")
erp_df   = spark.read.format("delta").load(".../raw_erp_contact")
csv_df   = spark.read.option("header", True).csv(".../files/contacts.csv")

# Tag each source before unioning
crm_df  = crm_df.withColumn("_source_system", lit("CRM"))
erp_df  = erp_df.withColumn("_source_system", lit("ERP"))
csv_df  = csv_df.withColumn("_source_system", lit("CSV"))

combined = union_with_schema_alignment([crm_df, erp_df, csv_df])

Power Query / M — Append Queries

// Append CRM and ERP contact tables
let
    CRM_Contacts  = Excel.Workbook(...){[Name="CRM"]}[Data],
    ERP_Contacts  = Csv.Document(...),
    // Align schemas before appending
    CRM_Aligned   = Table.SelectColumns(CRM_Contacts, {"Name","Email","Phone","Source"}),
    ERP_Aligned   = Table.SelectColumns(ERP_Contacts, {"Name","Email","Phone","Source"}),
    Combined      = Table.Combine({CRM_Aligned, ERP_Aligned})
in
    Combined