How to create a per-column Data Summarizer in Fabric Copilot
This tutorial shows how to create a per-column Data Summarizer in Fabric Copilot — a tool that automatically generates descriptive statistics, unique values and anomaly signals for each column of a table. It is useful for quickly exploring data, detecting issues and preparing reports or quality rules.
Prerequisites
- Account with access to Microsoft Fabric and permissions to create Workspaces and OneLake.
- Sample table loaded in the Lakehouse (e.g., dados_vendas) or access to a supported source.
- Basic knowledge of Power Query/Notebooks and familiarity with Copilot in Fabric.
Step 1: Prepare the table and context in Fabric
Open the Workspace where the table is located (e.g.: Lakehouse). Confirm the table name and the key column. Copilot needs clear context: table name, type of each column and a sample of rows works well.
Step 2: Start a new Copilot Prompt
In Fabric, open Copilot and choose the data interaction mode (Data view). Enter an initial prompt that describes the task: generate a per-column summary including count, missing, uniques, mean/median (for numerics), most frequent values (for categoricals) and potential anomalies.
Gera um Data Summarizer para a tabela 'dados_vendas'. Para cada coluna, devolve: nome, tipo, count, missing_count, unique_count, top_3_values (com proporção), mean, median, std (se numérica), e sinaliza potenciais anomalias (valores extremos ou formatos inválidos). Usa exemplo das primeiras 100 linhas.
Step 3: Provide sample and schema to Copilot
Copy a sample of 50-200 rows and the schema (names and types). Pasting this into the prompt helps the model avoid assumptions. If preferred, connect Copilot directly to the table if the integration allows.
Esquema:
- id: integer
- data_venda: datetime
- produto: string
- preco: decimal
- quantidade: integer
Amostra (5 linhas):
1, 2026-01-05, Camisola, 19.99, 2
2, 2026-01-06, Calças, 39.50, 1
...
Step 4: Refine the prompt for structured outputs (JSON/Table)
To automate consumption, ask Copilot to return the summary in JSON format or as a Markdown table. This allows transforming the result into rules, reports or importing it into Power BI. Define the output schema by field.
Output format: JSON array, cada item:
{
"column": "nome_coluna",
"type": "string|integer|decimal|datetime",
"count": 1000,
"missing": 5,
"unique": 120,
"top_values": [{"value":"X","pct":0.12}],
"mean": 12.34, // null se não aplicável
"median": 10.0,
"std": 3.21,
"anomalies": ["valores negativos","formato inválido"]
}
Step 5: Run and validate the Summarizer
Send the prompt with the sample and schema. Copilot will return the JSON/table. Manually verify some fields (e.g.: missing count and top_values) using a simple query to ensure the numbers match.
-- Exemplo SQL para validar missing e top values
SELECT
COUNT(*) AS total,
SUM(CASE WHEN preco IS NULL THEN 1 ELSE 0 END) AS missing_preco,
COUNT(DISTINCT produto) AS unique_produto
FROM dados_vendas;
SELECT produto, COUNT(*)*1.0/COUNT(*) OVER() AS pct
FROM dados_vendas
GROUP BY produto
ORDER BY COUNT(*) DESC
LIMIT 3;
Step 6: Turn the Summarizer into a reusable Prompt
Create a prompt template with placeholders for table, schema and sample size. Save it in Copilot as a Template to reuse with other tables. Include instructions for the output format (JSON) and the threshold for anomalies (e.g.: z-score > 3).
Template:
"Gera um Data Summarizer para a tabela '{tabela}'. Usa {n_amostra} linhas. Output em JSON. Marca anomalias quando z-score > {z_threshold}."
Check the result
Confirm that the JSON contains one entry per column with the fields populated. Validate key statistics (count, missing, unique) with direct queries. Test the template with another table to verify reuse. If proportions/most frequent values diverge, increase the sample or adjust the prompt.
Conclusion
With this Data Summarizer in Fabric Copilot you get a quick, structured view of columns, useful for profiling, creating quality rules and preparing reports. Next step: automate periodic prompt execution and integrate the output into a metrics table. Tip: start with a representative sample and adjust the z_threshold to reduce false positives.