Power Query动态扩展记录需求：适配Web服务返回的可变字段

Got it, let's fix that static column expansion headache so you don't have to manually update fields every time the API returns new properties in the $items array. Here's how to make the process fully dynamic:

The Core Problem #

Your current code uses hardcoded field names ({"id", "displayed_as", "$path"}) in Table.ExpandRecordColumn, which means any new fields like city, zip, or phone won't show up unless you manually add them. We need to pull field names directly from the records themselves instead of guessing.

Step-by-Step Solution #

Replace your static expansion step with code that automatically detects all available fields in the $items records:

let
    Quelle = Sage.Contents(),
    records = Quelle{[Name="Kontakte"]}[Data],
    #"SelectItems" = Table.SelectColumns(records,{"$items"}),
    #"$items1" = #"SelectItems"{0}[#"$items"],
    #"ToTable" = Table.FromList(#"$items1", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    // Dynamically get all field names from the first record (handles most cases)
    DynamicFieldNames = if List.Count(#"$items1") > 0 then Record.FieldNames(#"$items1"{0}) else {},
    // Expand using the dynamic field list, auto-generate column names
    #"ExpandedColumn" = Table.ExpandRecordColumn(#"ToTable", "Column1", DynamicFieldNames, List.Transform(DynamicFieldNames, each "Column1." & _))
in
    #"ExpandedColumn"

Key Changes Breakdown #

• DynamicFieldNames: This line checks if the $items list isn't empty, then uses Record.FieldNames to pull all property names from the first record. If the list is empty, it defaults to an empty array to avoid errors.
• Dynamic Expansion: The Table.ExpandRecordColumn now uses the auto-generated DynamicFieldNames instead of hardcoded values. We also use List.Transform to match your original column naming pattern (like Column1.id, Column1.displayed_as) automatically.

Edge Case Fix (For Inconsistent Records) #

If some records in $items have more fields than the first one, use this variant to collect all unique field names across every record:

// Alternative: Capture ALL unique fields from every record in $items
AllFieldNames = List.Distinct(List.Combine(List.Transform(#"$items1", Record.FieldNames))),
#"ExpandedColumn" = Table.ExpandRecordColumn(#"ToTable", "Column1", AllFieldNames, List.Transform(AllFieldNames, each "Column1." & _))

This ensures no fields get missed even if later records have extra properties the first one doesn't.

内容的提问来源于stack exchange，提问作者Cramble