Adiabatic Mixing Temperature Calculator: Temperature-Dependent Cp(T) Energy Balance Guide

on

Excel 365 Power Query: Build Powerful Custom Data Types for Smarter Analysis

on

This article explains how to design, create, and use Excel custom data types with Power Query so that you can turn flat tables into reusable, structured objects that make analysis, reporting, and self-service BI in Excel faster and more reliable.

1. Why Excel custom data types with Power Query matter

Traditional Excel models store information across many columns and tables, and then connect them with lookups, named ranges, and pivot tables.

  • A customer appears in multiple tables, each with different columns.
  • A product catalog is spread across several sheets.
  • Measures such as sales, margin, or last purchase date are recalculated repeatedly with complex formulas.

Custom data types built with Power Query allow you to encapsulate all of that information into a single cell that behaves like a structured object. Internally, the cell contains a record made up of many fields. On the worksheet, you can expose only the fields you need and reference them directly in formulas.

Compared to the built-in geography and stock data types, Power Query custom data types are created from your own tables, so you fully control the schema, logic, and refresh behavior.

2. Conceptual foundation: data types and structured values in Power Query

In the Power Query engine, every column has a data type such as text, number, date, logical, or datetime. Setting types early improves performance and prevents subtle errors when loading to Excel or the Data Model.

Power Query also supports structured value types:

  • List – an ordered collection of values.
  • Record – a set of named fields, similar to a row in a strongly typed table.
  • Table – a set of rows and columns, essentially a list of records.

Custom data types for Excel are ultimately based on Power Query records. When you create a custom data type from a table, Power Query collapses the selected columns into a single record value that is stored inside one column. Excel then exposes that record as a custom data type cell with a data type icon and a card view.

2.1 From table columns to a single structured cell

Assume you have a customer table:

  • Customer ID
  • Customer Name
  • Segment
  • Region
  • Sales YTD
  • Last Order Date

Using Power Query, you can group these columns into a single Customer custom data type. On the worksheet, each row then has one Customer cell that contains all those fields. You can extract any field, like Customer.Name or Customer.Sales YTD, as separate columns or refer to them directly in formulas.

3. Creating a custom data type from a Power Query table

The high-level workflow in Excel 365 and later is:

  1. Create or connect to a data source.
  2. Load it to Power Query and shape it.
  3. Select the fields that define the data type.
  4. Use Transform > Create Data Type in the Power Query Editor.
  5. Choose a display column and name the data type.
  6. Load the query back to the worksheet.

3.1 Preparing the source table

  1. Format your data as an Excel table (select any cell and press Ctrl+T).
  2. Ensure each row represents a single entity (customer, product, project, location, etc.).
  3. Check that column names are stable and descriptive, because they become field names in the custom data type.
  4. Remove calculated columns that you can compute later in Power Query; this keeps the schema clean.
Note : Design the table as a proper dimension or master table: one row per key (such as Customer ID or Product ID), no duplicate keys, and minimal derived columns. This produces cleaner custom data types and simpler formulas downstream.

3.2 Loading to Power Query

  1. Select any cell in the table.
  2. Go to the Data tab > From Table/Range to open the Power Query Editor.
  3. Power Query automatically infers data types; adjust them explicitly using the type icons or Transform > Data Type for full control.

3.3 Creating the custom data type in the Editor

  1. In the Power Query Editor, select the columns you want to include in your custom type (for example, all descriptive and metric fields for a customer).
  2. On the Transform tab, in the Structured Column group, choose Create Data Type.
  3. In the dialog box:
    • Provide a name for the data type, e.g., Customer.
    • Choose the display column (for example, Customer Name) that will appear in the Excel grid.
  4. Confirm the dialog. Power Query collapses the selected columns into a single column whose values are records, and marks that column as a custom data type.
  5. Use Home > Close & Load to load the query back into Excel. Each row becomes a cell displaying the chosen display column, with the custom data type icon.

3.4 Optional: building the record column manually in M

In advanced scenarios, you may want to construct the record before creating the data type. This is helpful when you want nested structures or when you are reshaping disparate sources.

let Source = Excel.CurrentWorkbook(){[Name = "tblCustomer"]}[Content], #"Changed Type" = Table.TransformColumnTypes( Source, { {"Customer ID", Int64.Type}, {"Customer Name", type text}, {"Segment", type text}, {"Region", type text}, {"Sales YTD", type number}, {"Last Order", type date} } ), #"Create Customer Record" = Table.AddColumn( #"Changed Type", "CustomerRecord", each [ ID = [Customer ID], Name = [Customer Name], Segment = [Segment], Region = [Region], SalesYTD = [Sales YTD], LastOrder = [Last Order] ] ) in #"Create Customer Record"

After this step, select CustomerRecord and use Create Data Type so that the record is promoted to a named custom data type for the Excel grid.

4. Working with custom data types on the worksheet

Once the query is loaded, you will see a column where each cell shows a text value (for example, the customer name) plus a data type icon. That icon indicates a custom data type cell. Clicking it usually opens a card view with all the fields inside that structured value.

4.1 Extracting fields into columns

Excel adds an Insert Data button near the header of the custom data type column. Use it to select fields you want to expand as new columns:

  1. Select the custom data type column header.
  2. Click Insert Data (the small icon with a column and arrow).
  3. Check the fields you want to materialize (for example, Segment, Region, SalesYTD).
  4. Excel adds those as new columns, preserving the connection to the underlying data type.

4.2 Referencing fields directly in formulas

You can also reference fields in formulas without expanding them into separate columns. Use the data type field access syntax:

=B2.[SalesYTD] =B2.[Region]

Here, B2 is a cell containing the custom data type, and [SalesYTD] is the field inside that data type. This allows you to combine custom data types with dynamic arrays, LET, FILTER, and other modern Excel functions.

4.3 Using custom data types in dynamic ranges and dashboards

Because each structured cell acts like a record, you can feed it directly into more advanced formulas, for example:

=LET( c, A2, sales, c.[SalesYTD], target, 1000000, TEXTJOIN(" ", TRUE, c.[Name], "is at", TEXT(sales, "#,##0"), "of", TEXT(target, "#,##0"), "YTD target." ) )

This pattern keeps all related data in one place, reduces the number of helper columns, and makes your report definitions easier to read and maintain.

5. Designing robust schemas for custom data types

The power of custom data types comes from careful schema design. A schema is the set of fields and their types inside the structured object.

Entity Typical key fields Descriptive attributes Metric fields
Customer Customer ID, Tax ID Name, Segment, Region, Sales Rep Sales YTD, Margin YTD, Last Order Date
Product SKU, Product ID Product Name, Category, Brand, UOM Standard Cost, List Price, Margin %
Vendor Vendor ID Vendor Name, Country, Currency On-time Delivery %, Average Lead Time
FX Rate Currency Pair, Date Source, Calendar Spot Rate, 30-day Average

5.1 Best practices for schema design

  • Use stable business keys, not volatile text labels, as identifiers.
  • Normalize repeated information; for example, store region once, not repeated across multiple related types.
  • Choose field names that are friendly for formulas (SalesYTD instead of Sales YTD (USD)).
  • Keep metrics that are needed in many places inside the type; compute rare or experimental metrics in separate measures.
Note : Once you start building reports that reference custom data type fields deeply, renaming or removing those fields can break formulas. Plan the schema first, then implement it in Power Query to avoid frequent structural changes.

6. Nested custom data types for hierarchical information

Power Query also supports nested custom data types, where a custom data type contains another custom data type as one of its fields.

For example:

  • Customer type contains a nested Address type.
  • Product type contains a nested Supplier type.
  • Project type contains nested Owner and Budget types.

The basic pattern is:

  1. Create a custom data type such as Address from an address table.
  2. In another query, use that existing custom data type as a field when you create the outer Customer data type.

This approach gives you multi-level card views in Excel and allows formulas like =A2.[Address].[City], mirroring hierarchical structures from relational databases or APIs.

7. Refresh, performance, and compatibility considerations

7.1 Refresh pipeline

Custom data types created via Power Query participate in the usual refresh pipeline:

  • They are updated when you refresh the underlying query or all connections.
  • They can be sourced from files, databases, web APIs, or combined sources.
  • They can co-exist with the Data Model and Power Pivot measures.

Because the Excel grid holds the custom data type values, large numbers of rows with very wide schemas can increase workbook size. Use nested structures judiciously and avoid storing rarely used fields.

7.2 Performance tips

  • Filter as early as possible in Power Query to reduce rows.
  • Trim the schema: only include fields that are needed in reports.
  • Use numeric types for measures (for example, number, currency) instead of storing everything as text.
  • Consider aggregating at an appropriate grain before loading, such as monthly totals per customer instead of transaction-level detail.

7.3 Compatibility and deployment

Note : Power Query custom data types are available only in modern Excel (Microsoft 365 and newer releases that include the feature). Users on older perpetual versions may see the data as static values or may not be able to work with the types interactively.

When sharing workbooks:

  • Confirm that recipients use Excel versions that support Power Query and custom data types.
  • Prefer sharing through OneDrive or SharePoint so that connections and refresh settings are preserved.
  • Document the data sources and refresh schedule in a separate “About” sheet.

8. Typical real-world scenarios

8.1 Customer 360 in a cell

Objective: give account managers a single cell per customer that contains all relevant context.

  • Source: CRM exports, invoicing system, support ticket logs.
  • Schema fields: profile (name, segment, region), financials (sales, margin, open AR), activity (last contact, open tickets).
  • Usage: dashboards that calculate account health scores, segment profitability, churn risk indicators, and pipeline conversion ratios.

8.2 Product catalog for pricing and margin analysis

Objective: make pricing models easier to read and maintain by encapsulating product information.

  • Source: ERP product master, costing system, promotions table.
  • Schema fields: SKU, description, category, brand, pack size, standard cost, list price, promo price, margin metrics.
  • Usage: quoting tools, what-if analysis sheets, category management dashboards.

8.3 Financial and risk data by instrument

Objective: treat each financial instrument as a single object with nested fields.

  • Schema fields: identifiers, issuer, ratings, coupon, maturity, yield measures, scenario stress indicators.
  • Usage: portfolio dashboards, limit monitoring, scenario analysis, and regulatory reporting prototypes.

8.4 Operational metrics with nested entities

Objective: model multi-level information such as site → line → asset in a single custom data type.

  • Use nested types like Site containing Line and Asset objects.
  • Attach KPI fields like uptime, MTBF, energy consumption, and maintenance cost.
  • Drive visual indicators and threshold calculations on the worksheet using direct field references.

FAQ

How is a Power Query custom data type different from a regular Excel table?

A regular Excel table stores each field in its own column, and formulas must reference columns separately. A Power Query custom data type collapses multiple fields into a single structured cell. You can still expand fields into columns, but the main benefit is that you can treat the entity as an object and reference its fields directly from formulas or card views. This reduces the number of visible columns and makes complex models more readable.

Do I always need Power Query to create custom data types?

For custom data types based on your own data, Power Query is the supported route because it provides the “Create Data Type” command and manages the connection, shaping, and refresh logic. Built-in data types like geography and stocks do not require Power Query, but they are limited to Microsoft-provided sources. If you need full control over the schema and source systems, you should use Power Query.

Can I edit values directly inside a custom data type cell?

No. The values inside a custom data type cell are controlled by the query that created them. To change them, you must either edit the underlying data source or modify the Power Query steps. On the worksheet, you can only choose which fields to display and how to reference them in formulas; you cannot safely overwrite the internals of the structured value from the grid.

How do I convert a custom data type back into regular columns?

You can always expand the custom data type into normal columns. Select the custom data type column header, use the Insert Data button, and choose all the fields. Excel will generate standard columns for each field that stay linked to the custom data type. If you need purely static values, you can copy the expanded columns and use Paste Special > Values. However, static values will no longer refresh when the query is refreshed.

When should I use the Data Model instead of custom data types?

The Data Model (Power Pivot) is ideal for large, star-schema-style models where you want to use DAX measures and PivotTables for aggregation. Custom data types shine when you want compact, object-like representations of entities directly in the grid and want to use regular Excel formulas, dynamic arrays, and charts without always going through PivotTables. In many cases, the best practice is to combine both: use the Data Model for heavy aggregations and custom data types for user-facing summaries, drill-through, and what-if analysis.

What happens if I rename or remove a field from the custom data type schema?

Formulas that reference that field may break or return errors because the field is no longer present in the underlying record. Likewise, card views may show fewer fields than before. For low-risk changes, update the schema and then systematically test reports that reference the affected data type. For critical models, consider versioning schemas, or introducing new fields alongside existing ones before fully deprecating the old names.

추천·관련글