Место назначения расширенных данных Excel (предварительный просмотр)

При работе с назначением, основанным на файлах, в Dataflow Gen2 у вас есть возможность сохранить данные в различных форматах, включая Excel. Файлы Excel можно создавать с помощью простых табличных данных, но можно также создавать сложные книги с несколькими листами, диаграммами и настраиваемым форматированием с помощью таблиц навигации. Эти таблицы навигации определяют структуру и содержимое документа Excel программным способом и обеспечивают эффективный способ создания динамических файлов Excel.

В этой статье объясняется, как создавать эти таблицы навигации для программного создания документов Excel из потока данных.

Необходимые условия

Прежде чем использовать расширенное назначение Excel, необходимо настроить поток данных 2-го поколения с правильной конфигурацией:

1. Создайте запрос таблицы навигации . В потоке данных 2-го поколения создайте запрос, который выводит таблицу навигации, как описано в этой статье. Запрос должен возвращать таблицу с необходимыми столбцами (PartType, Data, а также при необходимости Sheet, Name, и Properties).

2. Добавление назначения данных . Выберите запрос таблицы навигации и добавьте назначение данных, выбрав "Добавить назначение данных " в контекстном меню запроса или на ленте.

3. Выберите назначение на основе файлов. Выберите место назначения на основе файлов, например:

   • SharePoint
   • Lakehouse Files
   • Azure Data Lake Storage Gen2
   • Azure Blob-хранилище

4. Установите формат файла в Excel. В целевой конфигурации установите Формат файла на Excel.

5. Выберите расширенный формат . В раскрывающемся списке "Формат " выберите "Дополнительно". Этот параметр указывает соединителю интерпретировать выходные данные запроса как таблицу навигации, которая определяет структуру документов Excel.

   

После настройки при запуске потока данных система считывает таблицу навигации и создает соответствующий файл Excel со определенной структурой.

Обзор

Документы Excel в Power Query представлены в виде таблиц навигации — стандартных таблиц M с определенными столбцами, описывающими структуру документов. Каждая строка в таблице навигации представляет часть документа Excel, например лист, содержащий данные или диаграмму, визуализируя эти данные.

При настройке назначения выходных данных Excel в потоке данных система считывает эту таблицу навигации и создает соответствующий файл Excel. Такой подход обеспечивает гибкость в создании сложных рабочих книг с несколькими листами, диаграммами и связями данных.

Краткий справочник

В этом разделе представлено консолидированное представление типов частей и их ключевые свойства для быстрого поиска. Готовые шаблоны кода см. в разделе "Общие шаблоны". Сведения об устранении ошибок см. в разделе "Устранение неполадок".

Типы частей на первый взгляд

Ключевые свойства по типу части

* Для диаграмм с встроенными данными эти свойства управляют резервной таблицей данных.

Минимальный пример

Самый простой способ создания документа Excel — предоставить таблицу навигации только для экспортируемых данных. Все столбцы, кроме Data и PartType являются необязательными, соединитель может выводить отсутствующие свойства и создавать функциональный документ.

В следующем примере определяется SalesData таблица, используемая в этой статье:

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

Если имена листов или имен частей не указаны, система автоматически создает соответствующие значения по умолчанию.

Ссылка на другие запросы

В потоке данных можно ссылаться на другие существующие запросы в таблице навигации. Столбец Data принимает любое табличное значение, включая ссылки на запросы, определенные в другом месте потока данных:

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

Структура таблицы навигации

Таблица навигации соответствует определенной схеме со следующими столбцами:

Поддерживаемые типы частей

• Книга: Конфигурация на уровне документа. Если используется, должна быть первой строкой. Не содержит данных.
• SheetData: лист, содержащий табличные данные. Эта часть привязана к данным. Части SheetData должны содержать встроенные данные и не могут использовать ссылки на таблицы. Создает один диапазон данных, начиная с ячейки A1.
• Таблица: таблица Excel (отформатированная со стилями таблицы). Эта часть привязана к данным. Поддерживает явное позиционирование через StartCell и автоматическое позиционирование. Несколько частей таблицы могут существовать на одном листе, если они не перекрываются.
• Диапазон: диапазон данных без форматирования таблицы Excel. Эта часть привязана к данным. Поддерживает явное размещение с помощью StartCell и автоматическое позиционирование. Несколько частей диапазона могут существовать на одном листе, если они не перекрываются.
• Диаграмма: визуализация диаграммы. Можно разместить на отдельном листе или объединить на одном листе с данными. Эта часть привязана к данным , если она содержит встроенные данные или может ссылаться на данные из другой части.

Выбор подходящего типа части

Используйте это руководство по принятию решений, чтобы выбрать подходящий тип части для вашего сценария:

Используйте SheetData когда:

• Вам нужна простая, единая область данных на каждом листе
• Данные должны начинаться с ячейки A1
• Вам не нужны функции таблицы Excel (фильтры, структурированные ссылки)
• Вы экспортируете простые табличные данные

Используйте Table когда:

• Требуется форматирование таблицы Excel с раскрывающимся списком фильтров
• Для формул требуются структурированные ссылки
• Вы размещаете несколько регионов данных на одном листе
• Вам нужен точный контроль над позиционированием с помощью StartCell
• Вы хотите применить стили таблиц (TableStyleMedium1и т. д.)

Используйте Range когда:

• Вам нужны необработанные данные без форматирования таблицы Excel
• Вы хотите опустить заголовки (SkipHeader = true)
• Вы размещаете несколько регионов данных на одном листе
• Данные будут использоваться другими системами, ожидающими обычные диапазоны

Используйте Chart когда:

• Вам нужна визуализация данных
• С встроенными данными: диаграмма и данные отображаются вместе на одном листе
• Ссылки: диаграмма и данные могут находиться на отдельных листах, а несколько диаграмм могут совместно использовать один источник данных.

Пример с полной структурой

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

Диаграммы и размещение данных

При создании части диаграммы можно управлять отображением диаграммы и ее данных в книге.

Диаграмма с отдельным листом данных

Если диаграмма ссылается на данные из другой части с помощью синтаксиса meta [Name = "..."] , данные и диаграммы помещаются на отдельные листы.

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

Диаграмма с встроенными данными

Если часть диаграммы содержит данные напрямую (без ссылки на другую часть), данные и диаграмма создаются на одном листе:

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

Этот подход удобнее, если вы хотите, чтобы пользователи видели данные вместе с ее визуализацией.

Несколько диаграмм с встроенными данными на одном листе

Если несколько частей диаграмм имеют встроенные данные (а не ссылки), каждая диаграмма создает собственную таблицу резервных данных на одном листе. Система автоматически располагает эти вспомогательные таблицы, чтобы избежать перекрытия с помощью автоматического позиционирования. Внешний вид резервной таблицы можно контролировать, указав TableStyle, AutoPositionColumnOffset и AutoPositionRowOffset в настройках части диаграммы.

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

Части таблицы и диапазона

Table и Range части обеспечивают больше контроля над размещением данных, чем SheetData. Используйте их, если требуется несколько областей данных на одном листе или требуется определенное расположение.

Часть таблицы

Часть Table создает таблицу Excel с форматированием, раскрывающимся списком фильтров и структурированными ссылками. Используйте его при желании функций таблицы Excel.

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

Часть диапазона

Часть Range создает диапазон данных без форматирования таблицы Excel. Используйте его, если вам нужны необработанные данные без функций таблицы.

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

Несколько таблиц на одном листе

Вы можете разместить несколько частей Table или Range на одном листе, при условии что они не перекрываются.

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

Автоматическое позиционирование

Если вы не указываете StartCell (или не задаете его "auto"), система автоматически помещает части по вертикали, по одному ниже другого, в том порядке, в который они отображаются в таблице навигации. Это полезно при наличии нескольких регионов данных и не требует точного управления.

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

Чтобы изменить поведение позиционирования:

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

Перекрытия

Система обнаруживает и сообщает перекрывающиеся диапазоны. Если два компонента пытаются записать в одни и те же ячейки, возникает ошибка.

Чтобы избежать перекрытия:

• Использование автоматического позиционирования и разрешение системе упорядочивать части по вертикали
• При использовании явных StartCell значений убедитесь, что части не используют общие ячейки
• Учитывайте строку заголовка при вычислении позиций (таблицы всегда включают строку заголовка, в диапазонах это не обязательно).

Ширина столбца

Ширина столбцов вычисляется автоматически на основе содержимого и типов данных. Если несколько диапазонов используют одни и те же столбцы, самый верхний диапазон (по начальной строке) определяет ширину этих общих столбцов.

Свойство ShowGridlines

Свойство ShowGridlines определяет, отображаются ли линии сетки Excel для листа. Это свойство может быть задано на частях SheetData, Table, Range или Chart и влияет на весь лист. Когда true (по умолчанию) линии сетки видны. Когда falseлинии сетки скрыты.

Если какая-либо часть листа явно устанавливает ShowGridlines в false, то весь лист скрывает линии сетки.

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

Свойства рабочей книги

Тип Workbook части позволяет настроить параметры уровня документа. При использовании она должна быть первой строкой в таблице навигации.

Пример со свойствами рабочей тетради

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

Несколько диаграмм с общими данными

Диаграммы могут ссылаться на один источник данных, позволяя создавать несколько визуализаций одного набора данных без дублирования данных в книге.

Используйте синтаксис meta [Name = "..."] для создания ссылок. Столбец Name должен быть указан для части данных, чтобы диаграммы могли правильно интерпретировать ссылку.

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

Ссылки работают как в направлениях вперед, так и в обратном направлении— диаграмма может ссылаться на данные, определенные позже в таблице, и несколько диаграмм могут ссылаться на один и тот же источник данных.

Типовые фасеты и форматирование ячеек

Типы данных Power Query и их аспекты напрямую влияют на форматирование ячеек в созданном файле Excel. Система типов предоставляет широкие возможности форматирования, которые преобразуются в соответствующие числовые форматы Excel.

Базовые сопоставления типов

Использование аспектов типов для точного форматирования

Аспекты типов позволяют указать точность и масштаб для числовых значений:

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

Вы можете управлять точностью компонентов времени с помощью 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

Конфигурация диаграммы

Диаграммы настраиваются с помощью Properties записи. Вы можете явно указать параметры диаграммы или полагаться на подсистему вывода для определения соответствующих значений.

Свойства диаграммы

Расположение диаграммы

Свойство Bounds управляет размещением диаграммы и его размером. Можно указать границы в качестве текстового значения с помощью синтаксиса диапазона Excel или записи с подробными параметрами размещения.

Размещение по умолчанию

Если Bounds не указано, диаграммы размещаются в расположении по умолчанию, начиная с ячейки H8 с размером по умолчанию размером 8 столбцов в ширину и 16 строк. Несколько диаграмм на одном листе без явного Bounds будут перекрываться в этой позиции по умолчанию.

Текстовый формат (синтаксис диапазона Excel)

Используйте ссылку на одну ячейку для левого верхнего угла (размер по умолчанию 8 столбцов x 16 строк) или диапазон для явных измерений:

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

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

Формат записи с шириной и высотой

Укажите левый верхний угол и измерения:

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

Формат записи с явными углами

Кроме того, укажите оба угла области диаграммы:

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

Вспомогательные функции макета сетки

При размещении нескольких диаграмм на листе можно использовать вспомогательную функцию для вычисления позиций сетки:

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

Усовершенствованное позиционирование с смещениями EMU

Для точного расположения в ячейках используйте свойства смещения. Смещения находятся в ЭМУ (английских метрических единицах), где 914400 EMUs равно 1 дюйму.

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

Поддерживаемые типы диаграмм

Настройка DataSeries

Запись DataSeries определяет, как данные отображаются на элементах диаграммы.

* Параметр обязателен, если ChartInferenceFunction не настроен в свойствах рабочей книги. Без вывода, опущение AxisColumns вызывает ошибку "Нет предоставленных столбцов осей", а опущение ValueColumns вызывает ошибку "Нет предоставленных столбцов значений".

Пример с явной конфигурацией диаграммы

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

Многоуровневые диаграммы осей

Для данных иерархической категории можно указать несколько столбцов осей:

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

Использование функции Office.InferChartPropertiesGenerator

Функция Office.InferChartPropertiesGenerator создает подсистему вывода, которая автоматически определяет свойства диаграммы на основе данных. Эта возможность полезна, если требуется разумное значение по умолчанию, не указывая все сведения о конфигурации диаграммы.

Основное использование

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

Параметры вывода заключений

Office.InferChartPropertiesGenerator принимает необязательную запись со следующими параметрами:

Пример с параметрами вывода

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

Принцип работы вывода

Подсистема вывода возвращает запись с тремя функциями:

• ChartType: анализирует схему данных и определяет оптимальный тип диаграммы на основе:

  • Число рядов данных
  • Количество категорий
  • Независимо от того, являются ли столбцы осей datetime, числовыми или категориальными
  • Число строк

• ChartTitle: создает описательное название на основе типов диаграмм и имен столбцов.

• DataSeries: выводит ось и столбцы значений на основе схемы таблицы.

  • Столбцы Datetime предпочтительнее в качестве столбцов оси
  • Категориальные (текстовые) столбцы рассматриваются для осевых столбцов.
  • Числовые столбцы становятся столбцами значений

Логика выбора типа диаграммы

Подсистема вывода выбирает типы диаграмм на основе характеристик данных:

Настраиваемая функция вывода

Вы можете предоставить собственную функцию вывода, следующую за тем же интерфейсом:

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

Расширенные сценарии

В этом разделе рассматриваются программные подходы к созданию таблиц навигации динамически.

Секционирование данных на несколько листов

Список и функции таблиц Power Query можно использовать для динамического создания листов на основе значений данных.

Использование Table.Group

Группировать данные по столбцу и создавать лист для каждой группы:

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

Использование Table.Partition

Секционирование таблицы на группы на основе пользовательской хэш-функции. В этом примере данные продаж секционируется в сегменты доходов:

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

Пользовательская функция секционирования

Для получения дополнительных элементов управления используйте пользовательскую функцию секционирования:

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

Динамические диаграммы для секционированных данных

Объединение секционирования с диаграммами для создания визуализаций для каждой группы:

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

Распространенные шаблоны

В этом разделе приведены готовые шаблоны для частых сценариев. Скопируйте и адаптируйте эти шаблоны к вашим потребностям.

Шаблон: простой экспорт данных

Экспорт одной таблицы в Excel с минимальной конфигурацией:

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

Шаблон: несколько листов из списка

Создайте один лист на элемент в списке:

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

Шаблон: данные с диаграммой на одном листе

Создайте лист с данными и ее визуализацией:

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

Шаблон: панель мониторинга с несколькими диаграммами с общими данными

Создайте лист панели мониторинга с несколькими диаграммами, ссылающимися на один источник данных:

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

Шаблон: параллельные таблицы

Поместите две таблицы рядом друг с другом на одном листе:

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

Шаблон: сложенные таблицы с автоматическим размещением

Составление множества таблиц по вертикали с автоматическими расстояниями:

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

Шаблон: отчет без линий сетки

Создайте отполированный отчет со скрытыми линиями сетки:

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

Заметки и ограничения

Ограничения Excel

В Excel имеются ограничения, влияющие на создание документов:

• Максимальное количество строк на листе: 1 048 576 строк
• Максимальные столбцы на лист: 16 384 столбцов
• Максимальные символы на ячейку: 32 767 символов
• Максимальная длина листа: 31 символов
• Имена листов не могут содержать: \ / ? * [ ] или начало и конец с '

Если данные превышают эти ограничения, рассмотрите возможность секционирования на нескольких листах.

Обработка ошибок

Ошибки на уровне ячеек в данных приводят к сбою создания документов. Соединитель проверяет данные по мере обработки каждой строки, и при возникновении ошибок, таких как деление на ноль или ошибка преобразования типов, вызывает исключение.

Чтобы предотвратить сбои, выполните приведенные действия.

• Использование try...otherwise выражений для обработки потенциальных ошибок перед экспортом
• Замените значения ошибок соответствующими значениями по умолчанию или null
• Проверка качества данных перед созданием таблицы навигации

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

Обработка имен

Имена листов и частей автоматически очищаются для соответствия правилам именования Excel. Недопустимые символы удаляются, а имена усечены до 31 символов. Если StrictNameHandling включен в свойствах книги, то вместо автоматической очистки возникает ошибка.

Повторяющиеся имена листов автоматически создаются уникальными путем добавления числового суффикса.

Troubleshooting

В этом разделе рассматриваются распространенные ошибки, которые могут возникнуть при создании таблиц навигации и их устранении.

Справочник по ошибкам

Распространенные проблемы

Проблемы со структурой таблицы навигации

Проблема: создание документов завершается с ошибкой "Отсутствующее значение" или ошибками схемы.

Причина. Таблица навигации отсутствует обязательные столбцы или имеет неправильные типы столбцов.

Решение. Убедитесь, что таблица навигации содержит правильные столбцы с правильными типами:

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
]

Вывод диаграммы не работает

Проблема: диаграммы не удается создать, появляется ошибка "Без столбцов осей или значений".

Причина: Нет настроек ChartInferenceFunction и отсутствуют явные сопоставления столбцов.

Решение: Либо добавьте часть рабочей книги с функцией вывода данных в качестве первой строки:

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

Или явно укажите DataSeries для каждой диаграммы:

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

Ссылки на таблицы не разрешаются.

Проблема: ошибка "Ссылка на таблицу не найдена" при использовании ссылок на таблицы.

Причина. Метаданные Name ссылки не соответствуют какой-либо части данных.

Решение. Убедитесь, что имя метаданных совпадает точно:

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
    ...

Ошибки типа данных в ячейках

Проблема. Создание документов завершается сбоем при обработке определенных строк.

Причина. Данные содержат значения ошибок (например, деление по нулю или сбоям преобразования типов).

Решение. Очистка значений ошибок перед экспортом:

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

Неподдерживаемые значения даты или времени

Проблема. Ошибка "Предоставленное значение не представляется в Excel" при экспорте данных даты и времени.

Причина: Excel не может представлять даты до 1 января 1900 г. или продолжительности за пределами поддерживаемого диапазона.

Решение: отфильтруйте неподдерживаемые значения или преобразуйте их:

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

Ошибка смешивания sheetData и Table/Range

Проблема. Ошибка "Контейнер уже имеет часть SheetData" или "Контейнер уже содержит части диапазона или таблицы".

Причина. Вы не можете сочетать SheetData с Table или Range частями на одном листе.

Решение: либо используйте один SheetData для листа, либо используйте Table/Range части вместе.

// 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}

Конфликты автоматического размещения

Проблема. Ошибка "Компонент указывает AutoPositionColumnOffset или AutoPositionRowOffset, но имеет явный StartCell".

Причина: Вы не можете использовать смещения автоматического размещения, когда StartCell задано явно.

Решение: либо удалите StartCell для использования автоматического размещения, либо удалите свойства смещения:

// 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}

Недопустимые имена листов

Проблема. Ошибка при указании недопустимого имени контейнера или автоматической очистки имен.

Причина. Имена листов содержат недопустимые символы (\ / ? * [ ]) или превышают 31 символов.

Решение: Предварительно очистите имена листов:

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

Связанный контент

• спецификация языка Power Query M
• Справочник по функциям таблицы
• Потоки данных Power Query