Gen2 Dataflow Pattern

by

SharePoint list → cleaned, flat, ISO-dated table

A reusable Power Query (M) snippet for Dataflows Gen2 that:

  • Connects to a SharePoint list using SharePoint.Tables with the Gen2-friendly connector settings
  • Adds an ETL timestamp (dataflow_date)
  • Normalises column names for downstream systems (no spaces, slashes, #, &, brackets, or dots)
  • Removes nested columns (records/tables) to keep the landing table flat
  • Coerces Any-typed columns to text to reduce schema drift
  • Formats all date-like columns as ISO 8601 strings (yyyy-MM-dd HH:mm:ss)

When to use: Landing SharePoint list data into a Bronze/Silver layer (Fabric/Synapse/SQL) where flat, predictable schemas, text-formatted dates, and stable column names are preferred.

Full code (drop-in)

let
    // =====================
    // SETTINGS (edit me)
    // =====================
    SiteUrl = "https://bmtglobalorg.sharepoint.com/sites/mySite",   // Your site URL
    ListId  = "ItemGUID",                                           // The list's GUID or Id key from navigation

    // 1) Connect to SharePoint list (Gen2-compatible settings)
    Source = SharePoint.Tables(SiteUrl, [Implementation = "2.0", ViewMode = "Default"]),
    Navigation = Source{[Id = ListId]}[Items],

    // =====================
    //  Expand Columns and other local formatting actions goes here
    // =====================

   CustomSteps = Navigation,

    // =====================
    // Data Engineering Steps: Normalise complex columns
    // =====================

    Normalise = CustomSteps,

    // 2) Stamp ETL metadata (local timestamp)
    WithDataflowDate = Table.AddColumn(Normalise, "dataflow_date", each DateTime.LocalNow(), type datetime),

    // 3) Normalise column names (safe for SQL/Fabric)
    CleanNames = Table.TransformColumnNames(
        WithDataflowDate,
        each
            let
                trimmed   = Text.Trim(_),
                noSpaces  = Text.Replace(trimmed, " ", "_"),
                noDashes  = Text.Replace(noSpaces, "-", "_"),
                noSlashes = Text.Replace(noDashes, "/", "_"),
                noAmp     = Text.Replace(noSlashes, "&", "and"),
                noHash    = Text.Replace(noAmp, "#", ""),
                noBracks  = Text.Remove(noHash, {"(", ")", "[", "]"}),
                noDots    = Text.Replace(noBracks, ".", "_")
            in
                noDots
    ),

    // 4) Remove complex/nested columns (records/tables) to keep it flat
    TableSchema   = Table.Schema(CleanNames),
    ListTypeCols  = Table.SelectRows(TableSchema, each List.Contains({"Record.Type", "Table.Type"}, [TypeName]))[Name],
    RemovedNested = if List.Count(ListTypeCols) > 0 then Table.RemoveColumns(CleanNames, ListTypeCols) else CleanNames,

    // 5) Force Any-type columns to text (avoid drift when values vary by refresh)
    UpdatedSchema = Table.Schema(RemovedNested),
    AnyTypeCols   = Table.SelectRows(UpdatedSchema, each [TypeName] = "Any.Type")[Name],
    AnyToText     = if List.Count(AnyTypeCols) > 0
                    then Table.TransformColumnTypes(RemovedNested, List.Transform(AnyTypeCols, each {_, type text}))
                    else RemovedNested,

    // 6) Convert date-like columns to ISO-8601 strings (uniform text output)
    DateCols     = Table.SelectRows(UpdatedSchema, each List.Contains({"date", "datetime", "datetimezone"}, [Kind]))[Name],
    ISOFormatted = if List.Count(DateCols) > 0 then
                       Table.TransformColumns(
                           AnyToText,
                           List.Transform(DateCols, (col) => { col, each DateTime.ToText(_, "yyyy-MM-dd HH:mm:ss"), type text })
                       )
                   else AnyToText
in
    ISOFormatted

Step-by-step

  1. Connector settings
    Uses SharePoint.Tables(SiteUrl, [Implementation = "2.0", ViewMode = "Default"]) to align with Dataflows Gen2 behaviour.
  2. List selection
    Selects the target list by Id: Source{[Id = ListId]}[Items].
  3. ETL timestamp
    Adds dataflow_date with DateTime.LocalNow() for operational lineage and refresh audits.
  4. Column name normalisation
    Replaces spaces, dashes, slashes, & → and, removes # and brackets, and swaps dots for underscores. Keeps names stable and SQL-friendly.
  5. Flattening
    Drops columns whose Table.Schema reports TypeName of Record.Type or Table.Type (common for person fields, lookups, attachments). Avoids nested structures that break tabular landings.
  6. Type stabilisation
    Any columns typed as Any are explicitly cast to text to avoid refresh-time flips when values differ across rows/batches.
  7. Date normalisation
    All date-like columns (by Kind) are converted to ISO text (yyyy-MM-dd HH:mm:ss). This simplifies cross-system loading and later parsing.

Customisation points

  • SiteUrl / ListId: Point to the correct site and list. ListId can be a GUID or the navigation key shown in the navigator.
  • Timestamp: Swap DateTime.LocalNow() for DateTimeZone.UtcNow() if you prefer UTC.
  • Name rules: Extend the replacement rules in Table.TransformColumnNames (e.g., additional forbidden symbols).
  • Nested fields: If you need person/lookup info, expand those before step 4 and then normalise again.
  • Date format: Change the format string if your downstream requires a different ISO variant (e.g., include Z for UTC).

Validation checklist

  • The preview shows a single flat table (no “Record” or “Table” cells)
  • Column names are underscore-separated with no special symbols
  • dataflow_date appears and is populated
  • Date columns are text and in yyyy-MM-dd HH:mm:ss

Troubleshooting

  • Access denied / empty table: Verify credentials and that the account can read the list.
  • Missing list in navigator: Confirm ListId and the site URL; refresh the navigator to capture recent lists.
  • Person/lookup fields lost: Expand the nested columns you need before the removal step; then re-run the clean/ISO steps.
  • Date times off by timezone: Switch to UTC for the timestamp and/or convert list dates to UTC before formatting.

Notes

  • Keeping dates as text is intentional for lake/SQL landings; parse back to native types in semantic models as needed.
  • If you later add explicit schemas, lock final column types (e.g., Int64.Type, type number) after the cleaning steps.

Leave a Comment