Excel Avanceret Data Destination (Forhåndsvisning)

Når du arbejder på en filbaseret destination i Dataflow Gen2, har du mulighed for at gemme dine data i forskellige formater, herunder Excel. Excel-filer kan oprettes med simple tabeldata, men du kan også lave komplekse arbejdsbøger med flere ark, diagrammer og tilpasset formatering ved at bruge navigationstabeller. Disse navigationstabeller definerer strukturen og indholdet af Excel-dokumentet programmatisk og giver en kraftfuld måde at generere dynamiske Excel-filer på.

Denne artikel forklarer, hvordan man konstruerer disse navigationstabeller for at generere Excel-dokumenter programmatisk ud fra dit Dataflow.

Forudsætninger

Før du kan bruge Excel Advanced-destinationen, skal du sætte din Dataflow Gen2 op med den korrekte konfiguration:

1. Opret en navigationstabelforespørgsel - I din Dataflow Gen2 skal du oprette en forespørgsel, der udgiver en navigationstabel som beskrevet i denne artikel. Forespørgslen skal returnere en tabel med de nødvendige kolonner (PartType, Data, og valgfrit Sheet, Name, og Properties).

2. Tilføj en datadestination - Vælg din forespørgsel i navigationstabelen og tilføj en datadestination ved at vælge Tilføj datadestination i forespørgslens kontekstmenu eller båndet.

3. Vælg en filbaseret destination - Vælg en filbaseret destination såsom:

   • SharePoint
   • Lakehouse-arkiverne
   • Azure Data Lake Storage Gen2
   • Azure Blob Storage

4. Indstil filformatet til Excel - I destinationskonfigurationen skal du sætte filformatet til Excel.

5. Vælg Avanceret format - Vælg Avanceret i Format-dropdownmenuen. Denne mulighed fortæller connectoren, at den skal tolke dit forespørgselsoutput som en navigationstabel, der definerer Excel-dokumentets struktur.

   

Når dataflowet er konfigureret, læser systemet din navigationstabel og genererer den tilsvarende Excel-fil med den struktur, du har defineret.

Oversigt

Excel-dokumenter i Power Query repræsenteres som navigationstabeller – standard M-tabeller med specifikke kolonner, der beskriver dokumentstrukturen. Hver række i navigationstabellen repræsenterer en del af Excel-dokumentet, såsom et regneark med data eller et diagram, der visualiserer disse data.

Når du konfigurerer en Excel-outputdestination i et dataflow, læser systemet denne navigationstabel og genererer den tilsvarende Excel-fil. Denne tilgang giver fleksibilitet til at definere komplekse arbejdsbøger med flere ark, diagrammer og datarelationer.

Hurtig reference

Dette afsnit giver et samlet overblik over deltyper og deres nøgleegenskaber til hurtig opslag. For færdige kodeskabeloner, se Almindelige mønstre. For fejlløsning, se Fejlfinding.

Deltyper i et hurtigt overblik

Nøgleegenskaber efter komponenttype

* For diagrammer med inline-data styrer disse egenskaber backing datatabellen.

Minimalt eksempel

Den nemmeste måde at oprette et Excel-dokument på er at levere en navigationstabel med kun de data, du ønsker at eksportere. Alle kolonner undtagen Data og PartType er valgfrie – connectoren kan udlede manglende egenskaber og opbygge et funktionelt dokument.

Følgende eksempel definerer en SalesData tabel, der bruges gennem hele denne artikel:

let
    // Define your data with columns used throughout this article
    SalesData = #table(
        type table [
            Region = text, 
            Category = text, 
            Product = text, 
            Quarter = text,
            Revenue = number,
            Units = number
        ],
        {
            {"North", "Electronics", "Laptop", "Q1", 45000, 150},
            {"North", "Electronics", "Phone", "Q1", 32000, 400},
            {"North", "Furniture", "Desk", "Q1", 18000, 60},
            {"South", "Electronics", "Laptop", "Q1", 38000, 120},
            {"South", "Electronics", "Phone", "Q1", 28000, 350},
            {"South", "Furniture", "Chair", "Q1", 12000, 200},
            {"East", "Electronics", "Tablet", "Q2", 22000, 180},
            {"East", "Furniture", "Desk", "Q2", 15000, 50},
            {"West", "Electronics", "Phone", "Q2", 41000, 520},
            {"West", "Furniture", "Chair", "Q2", 9000, 150}
        }
    ),

    // Create the navigation table with minimal structure
    // Only Data and PartType are required
    excelDocument = #table(
        type table [PartType = nullable text, Data = any],
        {
            {"SheetData", SalesData}
        }
    )
in
    excelDocument

Når du ikke angiver arknavne eller delnavne, genererer systemet automatisk passende standardindstillinger.

Henvisning til andre forespørgsler

I en Dataflow kan du referere til andre eksisterende forespørgsler i din navigationstabel. Kolonnen Data accepterer enhver tabelværdi, inklusive referencer til forespørgsler, der er defineret andre steder i din Dataflow:

let
    // Reference existing queries named "MonthlySales" and "CurrentInventory"
    excelDocument = #table(
        type table [PartType = nullable text, Data = any],
        {
            {"SheetData", MonthlySales},
            {"SheetData", CurrentInventory}
        }
    )
in
    excelDocument

Navigationstabelstruktur

Navigationstabellen følger et specifikt skema med følgende kolonner:

Understøttede deletyper

• Arbejdsbog: Dokumentkonfiguration. Det må være første række, hvis det bruges. Indeholder ikke data.
• SheetData: Et arbejdsark med tabeldata. Denne del er databundet. SheetData-dele skal indeholde inline-data og må ikke bruge tabelreferencer. Opretter et enkelt dataområde, der starter ved celle A1.
• Tabel: En Excel-tabel (formateret med tabelstyling). Denne del er databundet. Understøtter eksplicit positionering via StartCell og automatisk positionering. Flere Table-dele kan eksistere på samme ark, hvis de ikke overlapper.
• Område: Et dataområde uden formatering i Excel-tabelen. Denne del er databundet. Understøtter eksplicit positionering via StartCell og automatisk positionering. Flere Range-dele kan eksistere på samme ark, hvis de ikke overlapper.
• Diagram: En diagramvisualisering. Kan placeres på sit eget ark eller kombineres med data. Denne del er databundet , når den indeholder inline-data, eller den kan referere til data fra en anden del.

Valg af den rigtige reservedelstype

Brug denne beslutningsguide til at vælge den passende deltype til din situation:

Brug SheetData når:

• Du har brug for et simpelt, enkelt dataområde pr. ark
• Data bør starte ved celle A1
• Du behøver ikke funktioner i Excel-tabellen (filtre, strukturerede referencer)
• Du eksporterer ligetil tabeldata

Brug Table når:

• Du vil have formatering af Excel-tabelen med filterdropdowns
• Du har brug for strukturerede referencer til formler
• Du placerer flere dataregioner på ét ark
• Du har brug for præcis kontrol over positionering med StartCell
• Du vil gerne anvende tabelstile (TableStyleMedium1osv.)

Brug Range når:

• Du har brug for rådata uden formatering af Excel-tabeller
• Du vil udelade headers (SkipHeader = true)
• Du placerer flere dataregioner på ét ark
• Dataene vil blive forbrugt af andre systemer, der forventer almindelige intervaller

Brug Chart når:

• Du har brug for datavisualisering
• Med inline-data: diagram og data vises samlet på det samme ark
• Med referencer: diagram og data kan være på separate ark, og flere diagrammer kan dele én datakilde

Eksempel med fuld struktur

let
    // Helper function to create a reference to another part
    buildReference = (name as text) => #table({}, {}) meta [Name = name],

    excelDocument = #table(
        type table [Sheet = nullable text, Name = nullable text, PartType = nullable text, Properties = nullable record, Data = any],
        {
            {"Workbook", "Workbook", "Workbook", [ChartInferenceFunction = Office.InferChartPropertiesGenerator()], null},
            {"DataSheet", "SalesTable", "SheetData", [], SalesData},
            {"ChartSheet", "SalesChart", "Chart", [ChartType = "Column"], buildReference("SalesTable")}
        }
    )
in
    excelDocument

Diagrammer og dataplacering

Når du opretter en diagramdel, kan du kontrollere, hvor diagrammet og dets data vises i arbejdsbogen.

Diagram med separat datablad

Når et diagram refererer til data fra en anden del ved hjælp af meta [Name = "..."] syntaksen, placeres dataene og diagrammet på separate ark.

let
    // Helper function to create a reference to another part
    buildReference = (name as text) => #table({}, {}) meta [Name = name],

    excelDocument = #table(
        type table [Sheet = nullable text, Name = nullable text, PartType = nullable text, Properties = nullable record, Data = any],
        {
            {"Workbook", "Workbook", "Workbook", [ChartInferenceFunction = Office.InferChartPropertiesGenerator()], null},
            // Data on its own sheet
            {"DataSheet", "SalesTable", "SheetData", [], SalesData},
            // Chart on a different sheet, referencing the data
            {"ChartSheet", "SalesChart", "Chart", [ChartType = "Line"], buildReference("SalesTable")}
        }
    )
in
    excelDocument

Diagram med indbyggede data

Når en diagramdel inkluderer data direkte (uden at referere til en anden del), oprettes både dataene og diagrammet på samme ark:

let
    excelDocument = #table(
        type table [Sheet = nullable text, Name = nullable text, PartType = nullable text, Properties = nullable record, Data = any],
        {
            {"Workbook", "Workbook", "Workbook", [ChartInferenceFunction = Office.InferChartPropertiesGenerator()], null},
            // Data and chart appear together on the same sheet
            {"SalesSheet", "SalesChart", "Chart", [ChartType = "Column"], SalesData}
        }
    )
in
    excelDocument

Denne tilgang er praktisk, når du ønsker, at brugerne skal se dataene sammen med visualiseringen.

Flere diagrammer med inline data på samme ark

Når flere diagramdele har inline-data (ikke referencer), opretter hvert diagram sin egen backing-datatabel på samme ark. Systemet positionerer automatisk disse backing tables for at undgå overlap ved hjælp af automatisk positionering. Du kan styre baggrundsbordets udseende ved at angive TableStyle, AutoPositionColumnOffset, og AutoPositionRowOffset på diagram-delen.

let
    ProductData = #table(
        type table [Product = text, Sales = number],
        {{"Widget", 100}, {"Gadget", 200}, {"Gizmo", 150}}
    ),

    RegionData = #table(
        type table [Region = text, Revenue = number],
        {{"North", 5000}, {"South", 3000}, {"East", 4000}}
    ),

    excelDocument = #table(
        type table [Sheet = nullable text, Name = nullable text, PartType = nullable text, Properties = nullable record, Data = any],
        {
            // First chart with inline data - backing table auto-positioned
            {"Dashboard", "ProductChart", "Chart",
                [
                    Bounds = "B2:H15",
                    ChartType = "Column",
                    ChartTitle = "Product Sales",
                    DataSeries = [AxisColumns = {"Product"}, ValueColumns = {"Sales"}]
                ],
                ProductData
            },
            // Second chart with inline data - backing table auto-positioned after first
            {"Dashboard", "RegionChart", "Chart",
                [
                    Bounds = "J2:P15",
                    ChartType = "Pie",
                    ChartTitle = "Revenue by Region",
                    TableStyle = "TableStyleLight1",  // Style for the backing table
                    AutoPositionRowOffset = 2,        // Add extra row gap from previous table
                    DataSeries = [AxisColumns = {"Region"}, ValueColumns = {"Revenue"}]
                ],
                RegionData
            }
        }
    )
in
    excelDocument

Tabel og Range-dele

Table og Range dele giver mere kontrol over dataplacering end SheetData. Brug dem, når du har brug for flere dataområder på samme ark eller har brug for specifik positionering.

Tabeldel

Delen Table opretter en Excel-tabel med formatering, filterdropdowns og strukturerede referencer. Brug den, når du vil have funktioner i Excel Table.

let
    excelDocument = #table(
        type table [Sheet = nullable text, Name = nullable text, PartType = nullable text, Properties = nullable record, Data = any],
        {
            // Table with explicit position and custom style
            {"Sales", "SalesTable", "Table",
                [StartCell = "B3", TableStyle = "TableStyleMedium9"],
                SalesData
            }
        }
    )
in
    excelDocument

Rækkevidde-delen

Delen Range opretter et dataområde uden formatering af Excel-tabellen. Brug det, når du har brug for rådata uden tabelfunktioner.

let
    excelDocument = #table(
        type table [Sheet = nullable text, Name = nullable text, PartType = nullable text, Properties = nullable record, Data = any],
        {
            // Range at a specific position
            {"Data", "DataRange", "Range",
                [StartCell = "D5"],
                SalesData
            }
        }
    )
in
    excelDocument

Flere tabeller på samme ark

Du kan placere flere Table eller Range dele på det samme ark, så længe de ikke overlapper.

let
    SummaryData = #table(type table [Metric = text, Value = number], {{"Total", 50000}, {"Average", 5000}}),

    excelDocument = #table(
        type table [Sheet = nullable text, Name = nullable text, PartType = nullable text, Properties = nullable record, Data = any],
        {
            // Two tables side by side
            {"Report", "DetailTable", "Table",
                [StartCell = "A1", TableStyle = "TableStyleMedium9"],
                SalesData
            },
            {"Report", "SummaryTable", "Table",
                [StartCell = "I1", TableStyle = "TableStyleLight15"],
                SummaryData
            }
        }
    )
in
    excelDocument

Automatisk positionering

Når du ikke specificerer StartCell (eller sætter det til "auto"), placerer systemet automatisk delene lodret, én under hinanden, i den rækkefølge de vises i navigationstabellen. Dette er nyttigt, når du har flere dataregioner og ikke behøver præcis kontrol.

let
    DataA = #table(type table [X = number], {{1}, {2}, {3}}),
    DataB = #table(type table [Y = number], {{10}, {20}}),
    DataC = #table(type table [Z = number], {{100}}),

    excelDocument = #table(
        type table [Sheet = nullable text, Name = nullable text, PartType = nullable text, Properties = nullable record, Data = any],
        {
            // First table: starts at B2 (column offset 1, row offset 1)
            {"AutoSheet", "Table1", "Table", [], DataA},
            // Second table: starts at B + (3 data rows + 1 header + 1 gap) = B7
            {"AutoSheet", "Table2", "Table", [], DataB},
            // Third table: continues below Table2
            {"AutoSheet", "Table3", "Table", [], DataC}
        }
    )
in
    excelDocument

For at ændre positioneringsadfærden:

let
    DataA = #table(type table [X = number], {{1}, {2}, {3}}),
    DataB = #table(type table [Y = number], {{10}, {20}}),

    excelDocument = #table(
        type table [Sheet = nullable text, Name = nullable text, PartType = nullable text, Properties = nullable record, Data = any],
        {
            // Start at column C (offset 2), row 4 (offset 3)
            {"CustomOffset", "Table1", "Table",
                [AutoPositionColumnOffset = 2, AutoPositionRowOffset = 3],
                DataA
            },
            // Start at column A (offset 0), with 2 row gap
            {"CustomOffset", "Table2", "Table",
                [AutoPositionColumnOffset = 0, AutoPositionRowOffset = 2],
                DataB
            }
        }
    )
in
    excelDocument

Overlap

Systemet registrerer og rapporterer overlappende afstande. Hvis to dele skriver til de samme celler, kastes en fejl.

For at undgå overlap:

• Brug automatisk positionering og lad systemet arrangere delene vertikalt
• Når du bruger eksplicitte StartCell værdier, skal du sikre dig, at delene ikke deler nogen celler
• Overvej header-rækken, når du beregner positioner (tabeller inkluderer altid en header-række, i intervaller er det valgfrit)

Kolonnebredder

Kolonnebredder beregnes automatisk baseret på indhold og datatyper. Når flere områder deler de samme kolonner, bestemmer det øverste område (ved startrække) kolonnebredden for de delte kolonner.

ShowGridlines-egenskaben

Egenskaben ShowGridlines styrer, om Excels gitterlinjer er synlige for et ark. Denne egenskab kan sættes på SheetData, Table, Range, eller Chart dele og påvirker hele arket. Når true (standarden), er gitterlinjer synlige. Når false, er gitterlinjer skjult.

Hvis en del på et ark eksplicit sætter ShowGridlines til false, skjuler hele arket gitterlinjer.

let
    excelDocument = #table(
        type table [Sheet = nullable text, Name = nullable text, PartType = nullable text, Properties = nullable record, Data = any],
        {
            // Hide gridlines for this sheet
            {"CleanReport", "DataTable", "Table",
                [ShowGridlines = false, StartCell = "B2"],
                SalesData
            }
        }
    )
in
    excelDocument

Arbejdsbogens egenskaber

Deltypen Workbook giver dig mulighed for at konfigurere indstillinger på dokumentniveau. Hvis den bruges, skal det være den første række i navigationstabellen.

Eksempel med arbejdsbogsegenskaber

let
    excelDocument = #table(
        type table [Sheet = nullable text, Name = nullable text, PartType = nullable text, Properties = nullable record, Data = any],
        {
            // Workbook configuration (must be first)
            {"Workbook", "Workbook", "Workbook", 
                [
                    ChartInferenceFunction = Office.InferChartPropertiesGenerator([
                        Allow3DCharts = false,
                        PreferMultilevelChartInference = true
                    ]),
                    StrictNameHandling = false
                ], 
                null
            },
            // Data and charts follow
            {"Sales", "SalesTable", "SheetData", [], SalesData}
        }
    )
in
    excelDocument

Flere diagrammer med delte data

Diagrammer kan referere til den samme datakilde, hvilket gør det muligt at lave flere visualiseringer af det samme datasæt uden at duplikere dataene i arbejdsbogen.

Brug syntaksen meta [Name = "..."] til at oprette referencer. Kolonnen Name skal specificeres for datadelen, så diagrammer kan løse referencen.

let
    // Helper function to create a reference
    buildReference = (name as text) => #table({}, {}) meta [Name = name],

    excelDocument = #table(
        type table [Sheet = nullable text, Name = nullable text, PartType = nullable text, Properties = nullable record, Data = any],
        {
            // Single data source - Name is required for reference resolution
            {"Data", "SalesTable", "SheetData", [], SalesData},
            
            // Multiple charts referencing the same data
            // Each chart must specify DataSeries with AxisColumns and ValueColumns
            {"LineChart", "TrendChart", "Chart", 
                [
                    ChartType = "Line", 
                    ChartTitle = "Sales Trend",
                    DataSeries = [AxisColumns = {"Quarter"}, ValueColumns = {"Revenue"}]
                ],
                buildReference("SalesTable")
            },
            {"PieChart", "DistributionChart", "Chart",
                [
                    ChartType = "Pie", 
                    ChartTitle = "Sales Distribution",
                    DataSeries = [AxisColumns = {"Region"}, ValueColumns = {"Revenue"}]
                ],
                buildReference("SalesTable")
            },
            {"BarChart", "ComparisonChart", "Chart",
                [
                    ChartType = "Bar", 
                    ChartTitle = "Product Comparison",
                    DataSeries = [AxisColumns = {"Product"}, ValueColumns = {"Revenue", "Units"}]
                ],
                buildReference("SalesTable")
            }
        }
    )
in
    excelDocument

Referencer arbejder både fremad og bagud – et diagram kan referere til data, der defineres senere i tabellen, og flere diagrammer kan referere til den samme datakilde.

Typefacetter og celleformatering

Power Query-datatyper og deres facetter påvirker direkte, hvordan celler formateres i den genererede Excel-fil. Typesystemet tilbyder rige formateringsmuligheder, der oversættes til passende Excel-talformater.

Grundlæggende typeafbildninger

Brug af skriftfacetter til præcis formatering

Typefacetter tillader dig at specificere præcision og skala for numeriske værdier:

let
    // Define types with specific facets
    currencyType = Type.ReplaceFacets(Currency.Type, 
        [NumericPrecisionBase = 10, NumericPrecision = 19, NumericScale = 4]),
    
    percentageType = Type.ReplaceFacets(Percentage.Type, 
        [NumericPrecisionBase = 10, NumericPrecision = 5, NumericScale = 2]),
    
    decimalType = Type.ReplaceFacets(Decimal.Type, 
        [NumericPrecisionBase = 10, NumericPrecision = 10, NumericScale = 4]),

    // Create table with typed columns
    tableType = type table [
        Product = Text.Type,
        Price = currencyType,           // Displays as currency with 4 decimal places
        Discount = percentageType,       // Displays as percentage with 2 decimal places
        TaxRate = decimalType           // Displays as number with 4 decimal places
    ],

    pricingData = #table(tableType, {
        {"Widget", 29.9999, 0.15, 0.0825},
        {"Gadget", 49.9500, 0.20, 0.0825}
    }),

    // Create navigation table with the typed data
    excelDocument = #table(
        type table [PartType = nullable text, Data = any],
        {
            {"SheetData", pricingData}
        }
    )
in
    excelDocument

DateTime-præcision

Du kan kontrollere præcisionen af tidskomponenter ved hjælp af facetten DateTimePrecision :

let
    // Time with millisecond precision
    timeWithMs = Type.ReplaceFacets(Time.Type, [DateTimePrecision = 3]),
    
    // DateTime with microsecond precision (Excel maximum is 3 digits)
    dateTimeWithPrecision = Type.ReplaceFacets(DateTime.Type, [DateTimePrecision = 7]),

    tableType = type table [
        EventName = Text.Type,
        EventTime = timeWithMs,
        Timestamp = dateTimeWithPrecision
    ],

    eventsData = #table(tableType, {
        {"Start", #time(9, 30, 15.123), #datetime(2025, 1, 15, 9, 30, 15.1234567)},
        {"End", #time(17, 45, 30.456), #datetime(2025, 1, 15, 17, 45, 30.9876543)}
    }),

    // Create navigation table with the typed data
    excelDocument = #table(
        type table [PartType = nullable text, Data = any],
        {
            {"SheetData", eventsData}
        }
    )
in
    excelDocument

Kortkonfiguration

Diagrammer konfigureres gennem registreringen Properties . Du kan eksplicit specificere diagramindstillinger eller stole på inferensmotoren til at bestemme passende værdier.

Hitlisteegenskaber

Placering på hitlisterne

Ejendommen Bounds bestemmer, hvor et diagram placeres, og dets størrelse. Du kan angive grænser som en tekstværdi ved hjælp af Excel-intervalsyntaks, eller som en post med detaljerede positioneringsmuligheder.

Standardplacering

Når det Bounds ikke er angivet, placeres diagrammerne på en standardplacering, der starter ved celle H8 med en standardstørrelse på 8 kolonner i bredden og 16 rækker i højden. Flere diagrammer på samme ark uden eksplicit Bounds vil overlappe på denne standardposition.

Tekstformat (Excel-områdesyntaks)

Brug en enkelt cellereference til øverste venstre hjørne (standardstørrelse på 8 kolonner x 16 rækker) eller et interval for eksplicitte dimensioner:

// Single cell: chart starts at B2 with default size
[Bounds = "B2"]

// Range: chart fills the area from G6 to N21
[Bounds = "G6:N21"]

Pladeformat med bredde og højde

Angiv øverste venstre hjørne og dimensioner:

[Bounds = [
    FromColumn = "B",   // or 1 for column B
    FromRow = 1,        // Row 2 (0-based)
    Width = 8,          // 8 columns wide
    Height = 16         // 16 rows tall
]]

Recordformat med eksplicitte hjørner

Alternativt angiv begge hjørner af kortets område:

[Bounds = [
    FromColumn = "A",
    FromRow = 0,
    ToColumn = "H",
    ToRow = 16
]]

Gitterlayout-hjælpefunktion

Når du placerer flere diagrammer på et ark, kan du bruge en hjælpefunktion til at beregne gitterpositioner:

let
    // Helper function to calculate chart bounds in a grid layout
    GetGridBounds = (
        chartNumber as number,       // 1-based chart number
        gridColumns as number,       // Number of columns in the grid
        optional width as number,    // Chart width (default: 8)
        optional height as number,   // Chart height (default: 16)
        optional hPadding as number, // Horizontal padding (default: 1)
        optional vPadding as number  // Vertical padding (default: 1)
    ) as record =>
        let
            w = width ?? 8,
            h = height ?? 16,
            hp = hPadding ?? 1,
            vp = vPadding ?? 1,
            cols = if gridColumns < 1 then 1 else gridColumns,

            x = Number.Mod(chartNumber - 1, cols) * (w + hp),
            y = Number.IntegerDivide(chartNumber - 1, cols) * (h + vp)
        in
            [FromColumn = x, FromRow = y, ToColumn = x + w, ToRow = y + h],

    buildReference = (name as text) => #table({}, {}) meta [Name = name],

    excelDocument = #table(
        type table [Sheet = nullable text, Name = nullable text, PartType = nullable text, Properties = nullable record, Data = any],
        {
            {"Data", "SalesTable", "SheetData", [], SalesData},
            // 2x2 grid of charts
            {"Dashboard", "Chart1", "Chart",
                [ChartType = "Column", Bounds = GetGridBounds(1, 2),
                 DataSeries = [AxisColumns = {"Region"}, ValueColumns = {"Revenue"}]],
                buildReference("SalesTable")},
            {"Dashboard", "Chart2", "Chart",
                [ChartType = "Line", Bounds = GetGridBounds(2, 2),
                 DataSeries = [AxisColumns = {"Region"}, ValueColumns = {"Revenue"}]],
                buildReference("SalesTable")},
            {"Dashboard", "Chart3", "Chart",
                [ChartType = "Pie", Bounds = GetGridBounds(3, 2),
                 DataSeries = [AxisColumns = {"Region"}, ValueColumns = {"Revenue"}]],
                buildReference("SalesTable")},
            {"Dashboard", "Chart4", "Chart",
                [ChartType = "Bar", Bounds = GetGridBounds(4, 2),
                 DataSeries = [AxisColumns = {"Region"}, ValueColumns = {"Revenue"}]],
                buildReference("SalesTable")}
        }
    )
in
    excelDocument

Avanceret positionering med EMU-forskydninger

For præcis placering inden for celler, brug offset-egenskaber. Offsets er i EMU'er (English Metric Units), hvor 914400 EMU'er svarer til 1 tomme.

[Bounds = [
    FromColumn = "C",
    FromColumnOffset = 352425,
    FromRow = 6,
    FromRowOffset = 142874,
    ToColumn = "R",
    ToColumnOffset = 142875,
    ToRow = 22,
    ToRowOffset = 171449
]]

Understøttede diagramtyper

DataSeries-konfiguration

Posten DataSeries definerer, hvordan dine data kortlægges til diagramelementer:

* Påkrævet, medmindre a ChartInferenceFunction er konfigureret i arbejdsbogens egenskaber. Uden slutning giver udeladelse AxisColumns en fejl "Ingen aksekolonner leveret", og udeladelse ValueColumns giver en fejl "Ingen værdikolonner angivet".

Eksempel med eksplicit diagramkonfiguration

let
    // Quarterly data by region
    quarterlyData = #table(
        type table [Quarter = text, North = number, South = number, East = number, West = number],
        {
            {"Q1", 95000, 78000, 37000, 50000},
            {"Q2", 102000, 85000, 42000, 58000}
        }
    ),

    excelDocument = #table(
        type table [Sheet = nullable text, Name = nullable text, PartType = nullable text, Properties = nullable record, Data = any],
        {
            {"SalesChart", "QuarterlySales", "Chart",
                [
                    ChartType = "StackedColumn",
                    ChartTitle = "Quarterly Revenue by Region",
                    DataSeries = [
                        AxisColumns = {"Quarter"},
                        ValueColumns = {"North", "South", "East", "West"}
                    ]
                ],
                quarterlyData
            }
        }
    )
in
    excelDocument

Flerlagsaksekort

For hierarkiske kategoridata kan du specificere flere aksekolonner:

let
    excelDocument = #table(
        type table [Sheet = nullable text, Name = nullable text, PartType = nullable text, Properties = nullable record, Data = any],
        {
            {"ProductChart", "Sales", "Chart",
                [
                    ChartType = "Column",
                    ChartTitle = "Product Sales by Category",
                    DataSeries = [
                        AxisColumns = {"Category", "Product"},
                        ValueColumns = {"Revenue"},
                        PrimaryAxisColumn = "Product"  // Use Product as the label
                    ]
                ],
                SalesData
            }
        }
    )
in
    excelDocument

Brug af Office.InferChartPropertiesGenerator

Funktionen Office.InferChartPropertiesGenerator opretter en inferensmotor, der automatisk bestemmer diagramegenskaber baseret på dine data. Denne funktion er nyttig, når du ønsker fornuftige standardindstillinger uden at specificere alle diagramkonfigurationsdetaljer.

Grundlæggende brug

let
    excelDocument = #table(
        type table [Sheet = nullable text, Name = nullable text, PartType = nullable text, Properties = nullable record, Data = any],
        {
            {"Workbook", "Workbook", "Workbook",
                [ChartInferenceFunction = Office.InferChartPropertiesGenerator()],
                null
            },
            // Charts without explicit ChartType will use inference
            {"Chart1", "AutoChart", "Chart", [], SalesData}
        }
    )
in
    excelDocument

Slutningsmuligheder

Office.InferChartPropertiesGenerator accepterer en valgfri post med følgende muligheder:

Eksempel med inferensmuligheder

let
    chartInference = Office.InferChartPropertiesGenerator([
        Allow3DCharts = true,
        PreferMultilevelChartInference = true
    ]),

    excelDocument = #table(
        type table [Sheet = nullable text, Name = nullable text, PartType = nullable text, Properties = nullable record, Data = any],
        {
            {"Workbook", "Workbook", "Workbook",
                [ChartInferenceFunction = chartInference],
                null
            },
            {"Sales", "SalesChart", "Chart", 
                [DataSeries = [AxisColumns = {"Region"}, ValueColumns = {"Revenue"}]],
                SalesData
            }
        }
    )
in
    excelDocument

Hvordan slutning fungerer

Inferensmotoren returnerer en post med tre funktioner:

• ChartType: Analyserer dataskemaet og bestemmer den optimale diagramtype baseret på:

  • Antal dataserier
  • Antal kategorier
  • Om aksekolonnerne er dato-, numeriske eller kategoriske
  • Rækkeantal

• ChartTitle: Genererer en beskrivende titel baseret på diagramtypen og kolonnenavnene.

• DataSeries: Udleder akse- og værdikolonner fra tabelskemaet:

  • Datokolonner foretrækkes som aksekolonner
  • Kategoriske (tekst) kolonner betragtes som aksekolonner
  • Numeriske kolonner bliver værdikolonner

Diagramtype-udvælgelseslogik

Inferensmotoren vælger diagramtyper baseret på datakarakteristika:

Brugerdefineret inferensfunktion

Du kan give din egen inferensfunktion, der følger samme grænseflade:

let
    customInference = () as record => [
        ChartType = (partName, columns, dataSeries, rowCount) => 
            if rowCount < 10 then "Pie" else "Column",
        
        ChartTitle = (partName, chartType, dataSeries) => 
            partName & " Chart",
        
        DataSeries = (partName, columns) => [
            AxisColumns = {"Category"},
            ValueColumns = {"Revenue"}
        ]
    ],

    excelDocument = #table(
        type table [Sheet = nullable text, Name = nullable text, PartType = nullable text, Properties = nullable record, Data = any],
        {
            {"Workbook", "Workbook", "Workbook",
                [ChartInferenceFunction = customInference],
                null
            },
            {"Data", "CustomChart", "Chart", [], SalesData}
        }
    )
in
    excelDocument

Avancerede scenarier

Dette afsnit dækker programmatiske tilgange til dynamisk generering af navigationstabeller.

Opdeling af data i flere ark

Du kan bruge Power Querys liste- og tabelfunktioner til dynamisk at oprette ark baseret på dataværdier.

Brug af Table.Group

Gruppere data i en kolonne og oprette et ark for hver gruppe:

let
    groupedData = Table.Group(
        SalesData, 
        {"Region"}, 
        {{"RegionData", each _, type table}}
    ),

    excelDocument = Table.FromRecords(
        List.Transform(
            Table.ToRecords(groupedData),
            (row) => [
                Sheet = row[Region],
                PartType = "SheetData",
                Data = row[RegionData]
            ]
        )
    )
in
    excelDocument

Brug af Table.Partition

Partitioner en tabel i grupper baseret på en brugerdefineret hashfunktion. Dette eksempel opdeler salgsdata i indtægtskategorier:

let
    Low_Revenue_Group_Index = 0,
    Medium_Revenue_Group_Index = 1,
    High_Revenue_Group_Index = 2,
    NumberOfPartitions = 3,

    RevenueRangeHash = (revenue as number) as number => 
        if revenue >= 40000 then High_Revenue_Group_Index
        else if revenue >= 25000 then Medium_Revenue_Group_Index
        else Low_Revenue_Group_Index,
    
    PartitionedList = Table.Partition(
        SalesData, 
        "Revenue", 
        NumberOfPartitions, 
        RevenueRangeHash 
    ),
    
    BucketNames = {
        "Low Revenue (< $25K)",      
        "Medium Revenue ($25K - $40K)", 
        "High Revenue (> $40K)"    
    },
    
    NamedPartitions = Table.FromColumns(
        {BucketNames, PartitionedList},
        type table [Sheet = text, Data = table]
    ),

    excelDocument = Table.AddColumn(NamedPartitions, "PartType", each "SheetData", type text)
in
    excelDocument

Brugerdefineret partitioneringsfunktion

For mere kontrol, brug en brugerdefineret partitioneringsfunktion:

let
    partitionByColumn = (table as table, columnName as text, maxPartitions as number) as table =>
        let
            distinctValues = List.Distinct(Table.Column(table, columnName)),
            limitedValues = List.FirstN(distinctValues, maxPartitions),
            partitionedTable = Table.FromRecords(
                List.Transform(
                    limitedValues, 
                    (value) => [
                        Sheet = columnName & " - " & Text.From(value),
                        PartType = "SheetData",
                        Data = Table.SelectRows(table, each Record.Field(_, columnName) = value)
                    ]
                )
            )
        in
            partitionedTable,

    // Create sheets for each unique Region value (up to 10)
    excelDocument = partitionByColumn(SalesData, "Region", 10)
in
    excelDocument

Dynamiske diagrammer for opdelte data

Kombiner opdeling med diagrammer for at lave visualiseringer for hver gruppe:

let
    buildReference = (name as text) => #table({}, {}) meta [Name = name],

    createPartitionWithChart = (table as table, columnName as text) as table =>
        let
            distinctValues = List.Distinct(Table.Column(table, columnName)),
            partitionRows = List.Transform(
                distinctValues,
                (value) => 
                    let
                        partitionData = Table.SelectRows(table, each Record.Field(_, columnName) = value),
                        sheetName = Text.From(value),
                        dataName = "Data_" & sheetName,
                        chartName = "Chart_" & sheetName
                    in
                        {
                            {sheetName, dataName, "SheetData", [], partitionData},
                            {sheetName & " Chart", chartName, "Chart", 
                                [ChartType = "Column", ChartTitle = sheetName & " Analysis"],
                                buildReference(dataName)}
                        }
            ),
            workbookRow = {{"Workbook", "Workbook", "Workbook", [ChartInferenceFunction = Office.InferChartPropertiesGenerator()], null}}
        in
            #table(
                type table [Sheet = nullable text, Name = nullable text, PartType = nullable text, Properties = nullable record, Data = any],
                List.Combine({workbookRow} & partitionRows)
            ),

    excelDocument = createPartitionWithChart(SalesData, "Region")
in
    excelDocument

Almindelige mønstre

Denne sektion indeholder færdige skabeloner til hyppige scenarier. Kopier og tilpas disse mønstre til dine behov.

Mønster: Simpel dataeksport

Eksporter en enkelt tabel til Excel med minimal konfiguration:

let
    excelDocument = #table(
        type table [PartType = nullable text, Data = any],
        {{"SheetData", YourDataTable}}
    )
in
    excelDocument

Mønster: Flere ark fra en liste

Opret ét ark pr. punkt i en liste:

let
    // Assume you have a list of {Name, Table} pairs
    dataSets = {
        {"Sales", SalesTable},
        {"Inventory", InventoryTable},
        {"Customers", CustomersTable}
    },
    
    excelDocument = #table(
        type table [Sheet = text, PartType = text, Data = table],
        List.Transform(dataSets, each {_{0}, "SheetData", _{1}})
    )
in
    excelDocument

Mønster: Data med skema på samme ark

Lav et ark med både data og visualisering:

let
    excelDocument = #table(
        type table [Sheet = nullable text, Name = nullable text, PartType = nullable text, Properties = nullable record, Data = any],
        {
            {"Workbook", "Workbook", "Workbook", [ChartInferenceFunction = Office.InferChartPropertiesGenerator()], null},
            {"Report", "SalesChart", "Chart", 
                [ChartType = "Column", Bounds = "F2:M18"], 
                YourDataTable
            }
        }
    )
in
    excelDocument

Mønster: Dashboard med flere diagrammer, der deler data

Opret et dashboardark med flere diagrammer, der refererer til én datakilde:

let
    ref = (name as text) => #table({}, {}) meta [Name = name],
    
    excelDocument = #table(
        type table [Sheet = nullable text, Name = nullable text, PartType = nullable text, Properties = nullable record, Data = any],
        {
            {"Data", "SourceData", "SheetData", [], YourDataTable},
            {"Dashboard", "Chart1", "Chart", 
                [ChartType = "Column", Bounds = "A1:H16", 
                 DataSeries = [AxisColumns = {"Category"}, ValueColumns = {"Value1"}]], 
                ref("SourceData")},
            {"Dashboard", "Chart2", "Chart", 
                [ChartType = "Line", Bounds = "J1:Q16", 
                 DataSeries = [AxisColumns = {"Category"}, ValueColumns = {"Value2"}]], 
                ref("SourceData")}
        }
    )
in
    excelDocument

Mønster: Side-om-side borde

Placer to borde ved siden af hinanden på samme ark:

let
    excelDocument = #table(
        type table [Sheet = nullable text, Name = nullable text, PartType = nullable text, Properties = nullable record, Data = any],
        {
            {"Report", "MainData", "Table", 
                [StartCell = "A1", TableStyle = "TableStyleMedium2"], 
                MainTable},
            {"Report", "Summary", "Table", 
                [StartCell = "H1", TableStyle = "TableStyleLight15"], 
                SummaryTable}
        }
    )
in
    excelDocument

Mønster: Stablede borde med automatisk positionering

Stab flere borde vertikalt med automatisk afstand:

let
    excelDocument = #table(
        type table [Sheet = nullable text, Name = nullable text, PartType = nullable text, Properties = nullable record, Data = any],
        {
            {"Report", "Section1", "Table", [], Table1},
            {"Report", "Section2", "Table", [AutoPositionRowOffset = 2], Table2},
            {"Report", "Section3", "Table", [AutoPositionRowOffset = 2], Table3}
        }
    )
in
    excelDocument

Mønster: Ren rapport uden gitterlinjer

Opret en poleret rapport med skjulte gitterlinjer:

let
    excelDocument = #table(
        type table [Sheet = nullable text, Name = nullable text, PartType = nullable text, Properties = nullable record, Data = any],
        {
            {"Report", "Data", "Table", 
                [StartCell = "B2", ShowGridlines = false, TableStyle = "TableStyleMedium9"], 
                YourDataTable}
        }
    )
in
    excelDocument

Bemærkninger og begrænsninger

Excel-grænser

Excel har iboende begrænsninger, der påvirker dokumentgenerering:

• Maksimalt antal rækker pr. arbejdsark: 1.048.576 rækker
• Maksimalt antal kolonner pr. arbejdsark: 16.384 kolonner
• Maksimalt antal tegn pr. celle: 32.767 tegn
• Arkets navn maksimal længde: 31 tegn
• Arknavne kan ikke indeholde: \ / ? * [ ] eller starte/slutte med '

Når dine data overstiger disse grænser, bør du overveje at opdele dem på flere ark.

Fejlhåndtering

Celleniveau-fejl i dine data får dokumentgenerering til at fejle. Connectoren validerer data, mens den behandler hver række, og hvis den støder på en fejlværdi (såsom division med nul eller typekonverteringsfejl), udløser en undtagelse.

For at forhindre fejl:

• Brug try...otherwise udtryk til at håndtere potentielle fejl før eksport
• Erstat fejlværdier med passende standardværdier eller nullværdier
• Valider datakvaliteten, før du bygger din navigationstabel

let
    RawData = #table(
        type table [Product = text, Amount = text],
        {
            {"Laptop", "1250.50"},
            {"Phone", "N/A"},
            {"Tablet", "850.00"}
        }
    ),
    
    cleanData = Table.TransformColumns(
        RawData,
        {{"Amount", each try Number.From(_) otherwise null, type nullable number}}
    ),
    
    excelDocument = #table(
        type table [PartType = nullable text, Data = any],
        {{"SheetData", cleanData}}
    )
in
    excelDocument

Navnehåndtering

Ark- og delnavne bliver automatisk renset for at overholde Excels navngivningsregler. Ugyldige tegn fjernes, og navne forkortes til 31 tegn. Hvis er StrictNameHandling aktiveret i Arbejdsbogens egenskaber, kastes en fejl i stedet for automatisk sanitering.

Duplikerede arknavne gøres automatisk unikke ved at tilføje et numerisk suffiks.

Fejlfinding

Dette afsnit dækker almindelige fejl, du kan støde på, når du opbygger navigationstabeller, og hvordan du løser dem.

Fejlreference

Almindelige problemer

Navigationstabellstrukturproblemer

Problem: Dokumentgenerering fejler med "Manglende værdi" eller skemafejl.

Årsag: Navigationstabellen mangler de nødvendige kolonner eller har forkerte kolonnetyper.

Løsning: Verdsig, at din navigationstabel indeholder de korrekte kolonner med de korrekte typer:

type table [
    Sheet = nullable text,        // Optional: worksheet name
    Name = nullable text,         // Unique identifier for the part
    PartType = nullable text,     // "Workbook", "SheetData", "Table", "Range", or "Chart"
    Properties = nullable record, // Configuration options
    Data = any                    // Table, table reference, or null
]

Diagraminferens virker ikke

Problem: Diagrammer kan ikke genereres med fejl som "Ingen akse/værdi-kolonner leveret".

Årsag: IngenChartInferenceFunction er konfigureret, og eksplicitte kolonnemappinger mangler.

Løsning: Enten tilføj en arbejdsbogsdel med inferensfunktionen som første række:

{"Workbook", "Workbook", "Workbook", [ChartInferenceFunction = Office.InferChartPropertiesGenerator()], null}

Eller specificér DataSeries eksplicit for hvert diagram:

[DataSeries = [AxisColumns = {"Category"}, ValueColumns = {"Revenue", "Profit"}]]

Tabelreferencer løses ikke

Problem: "Referencet tabel blev ikke fundet" fejl ved brug af tabelreferencer.

Årsag: Metadataene Name på referencen matcher ikke nogen datadel.

Løsning: Sørg for, at metadatanavnet matcher nøjagtigt:

let
    // Data part with name "SalesData"
    dataRow = {"Sales", "SalesData", "SheetData", [], actualTable},
    
    // Chart referencing the data - name must match
    chartRow = {"Chart", "SalesChart", "Chart", [], #table({}, {}) meta [Name = "SalesData"]}
in
    ...

Datatypefejl i celler

Problem: Dokumentgenerering fejler, når visse rækker behandles.

Årsag: Dine data indeholder fejlværdier (såsom division med nul eller fejl i typekonvertering).

Løsning: Rens fejlværdier før eksport:

Table.TransformColumns(
    YourTable,
    {{"ColumnName", each try _ otherwise null}}
)

Uunderstøttede dato- eller tidspunktværdier

Problem: Fejl "Den angivne værdi kan ikke repræsenteres i Excel" ved eksport af dato-/tidsdata.

Årsag: Excel kan ikke repræsentere datoer før 1. januar 1900 eller varigheder uden for det understøttede område.

Løsning: Filtrer ikke-understøttede værdier fra eller transformér dem:

Table.SelectRows(YourTable, each [DateColumn] >= #date(1900, 1, 1))

SheetData og tabel-/områdeblandingsfejl

Problem: Fejl "Container har allerede en SheetData-del" eller "Container har allerede Range- eller Table-dele."

Årsag: Du kan ikke kombinere SheetData med Table eller Range dele på samme ark.

Løsning: Enten bruger SheetData du alene til et ark, eller bruger Table/Range delene sammen:

// Option 1: Use SheetData alone
{"Sheet1", "Data", "SheetData", [], myTable}

// Option 2: Use Table/Range parts for multiple regions
{"Sheet1", "Table1", "Table", [StartCell = "A1"], firstTable},
{"Sheet1", "Table2", "Table", [StartCell = "F1"], secondTable}

Konflikter med automatisk positionering

Problem: Fejl "Part specificerer AutoPositionColumnOffset eller AutoPositionRowOffset, men har en eksplicit StartCell."

Årsag: Du kan ikke bruge automatisk positioneringsforskydning, når StartCell den er eksplicit sat.

Løsning: Fjern enten StartCell for at bruge autopositionering, eller fjern offset-egenskaberne:

// Auto positioning with custom offsets (no StartCell)
{"Sheet1", "Table1", "Table", [AutoPositionColumnOffset = 2, AutoPositionRowOffset = 1], myTable}

// Or explicit positioning (no offset properties)
{"Sheet1", "Table1", "Table", [StartCell = "C2"], myTable}

Ugyldige arknavne

Problem: Fejl med henvisning til ugyldigt containernavn eller automatisk navnesinficering.

Årsag: Arknavne indeholder ugyldige tegn (\ / ? * [ ]) eller overstiger 31 tegn.

Løsning: Fordesinficér navnene på dine ark:

sanitizeName = (name as text) as text =>
    let
        cleaned = Text.Replace(Text.Replace(name, "/", "-"), "\", "-"),
        truncated = Text.Start(cleaned, 31)
    in
        truncated

Relateret indhold

• M-sprogspecifikation for Power Query
• Tabelfunktionsreference
• Power Query-dataflows