Rethinking Data Prep: Power BI Narrative Visual as a Clever Ally

[jennratten]

Introduction

In this post, I'll walk you through how I pushed the Narratives visual beyond the traditional uses and into the unconventional frontier. I use it to parse, adapt, document, model, explain, infer and code. From common data engineering and analyst tasks to far-reaching edge cases, Narratives became an unexpected AI-powered ally with results displayed in a Power BI report.

Classic Scenario

A straightforward request comes your way – prepare some data for reporting and analysis.

You encounter a classic data hurdle – transform complex, nested source data into a clean, documented, and analysis-ready model – with minimal prior knowledge of the dataset's structure.

Use Challenges to Fuel Innovation

I set my bar high and went to work.

Objective: Display messy, unparsed data as a clean table with user-friendly column headers in a report visual with ZERO transformations or modeling.

Turn cringe-worthy data that most would prefer not to see, into reusable components that will evoke double-takes with minimal effort.

The Narrative Twist

I used Power BI's Narrative visual to interpret the data structure. With a bit of creative prompting the visual returned:

• A list of proposed tables (e.g., SalesFact, CustomerDim, ProductDim)
• Key relationships (e.g., CustomerID linking SalesFact to CustomerDim)
• Suggested measures like Total Sales Amount and Average Sale Amount
• A code block to define the model structure in TMDL-style syntax

This was all it took for the ideas started rushing in.

How It Works

The purpose of the Power BI Narrative is to generate insights automatically for business users. The Copilot Narrative is an AI-powered feature that automatically generates dynamic, context-aware summaries of data insights within a report.

The Smart Narrative visual in Power BI is built on three key layers:

• Data Layer (Semantic Model): This is where your measures, calculations, and relationships live. It provides the structured data foundation that the narrative uses to generate insights.
• Visual Layer (Narrative Visual): This is the component you add to your report. It dynamically displays text summaries that respond to filters and slicers, making your insights conversational and easy to interpret.
• AI Layer (Copilot): Copilot powers the natural language generation behind the scenes. It interprets your data context and creates meaningful, human-readable narratives automatically.

There is also a Custom option, which leverages Microsoft Cognitive Services. However, for most use cases, and the examples in this post, Copilot is the recommended choice because it is deeply integrated and optimized for Power BI.

Data Sources

I have three different large, unparsed, nested JSON values, which I imported to the model as text, so a variety of scenarios can be covered: product revenue summaries, sales data and insurance plan benefits. All three datasets are comprised of fake data that I had Copilot generate (aka synthesize) for me.

Setting up the Report

Add Source Data to Compatible Report Visuals

To ensure all relevant data is included, I added each data source to a table visual.

The Narrative visual works with most visuals that expose data from the semantic model, such as tables, cards, and bar charts. It does not support elements that do not directly bind to semantic model data-such as shapes, images, Q&A, and other Narrative visuals-because these objects do not contain measures or fields that can be summarized. Read more about considerations and limitations in the docs here: Smart Narrative Summaries - Considerations and Limitations

Add a Narrative Visual to the Canvas

Select the Visuals the Narrative Should Use

 

Add a Prompt

 

Reusable Prompt Framework

This is the basic framework for what to enter into the Copilot Narrative visual - I'll elaborate on this with variations in another post:

Task Overview: 
A high-level description of the objective. 

Output Structure:

1. A few words identifying the section's contents
- Title → description of desired output
- ...

(Repeat as needed.)

Notes: 
- Specific appearance requirement
- ...

 

Check Out Examples

 

Generate a Semantic Model Blueprint

Analyze unparsed data and create a blueprint for a semantic model includingfact tables,dimension tables,identification of primary keys, foreign keys and relationships,recommended measures, and code block to create the model.

Prompt

Result

Task Overview: Normalize nested JSON into fact and dimension tables and generate TMDL code. Output Structure: 1. Performance - Confidence Badge → color filled circle icon - Time saved → approximate hour range 2. Tables & Relationships → Table name (Fact/Dimension), - Columns: comma delimited list truncated after 5 column names (e.g. Column1, Column2... and {{n}} others) - 🔑 Primary Key: - 🔒Foreign Keys: list with relationships 3. Recommended Measures → list measures. 4. TMDL Code Block → TMDL script. Notes: - Use clear section headers and spacing; bold text for outline level 1 and level 2; all uppercase letters for outline level 1. Include line spacing before each section level 1. - When icons are used they should appear to the left of text - Include icons.

Adaptive Flattening

Let the model choose the best flattening strategy for the data (dot paths, explode arrays, pivot keys, etc.). Flatten the structure into one row per key, keeping key-level fields as columns. Explain the logic and other strategies considered. Let the model rename columns using a best guess.

Prompt

Result

Task Overview: Flatten nested JSON to one row per order item using best-fit strategy. Output Structure: 1. Confidence/Efficiency - Confidence Badge → color filled circle icon - Time saved → approximate hour range 2. Ranked top 3 flattening strategies with pros/cons; - table preview; - Python flattening script. Notes: - Use clear section headers and spacing; bold text for outline level 1 and level 2; all uppercase letters for outline level 1. Include line spacing before each section level 1. - When icons are used they should appear to the left of text - When the top three items are ranked use medal icons for the items and Rank as the title.

Data Harmonization

Normalize and harmonize nested JSON data by standardizing date formats to ISO 8601 and aligning currency exchange rates under a unified USD base. Address structural inconsistencies, identify missing transactional values, and provide recommendations to improve data quality and enable accurate aggregation.

Prompt

Result

Task Overview: Harmonize and summarize sales data stored as nested JSON. Repair data types, unify currencies and units, and prepare a sample for visualization evaluation. Output Structure: 1. Quality and Consistency Notes → Identify missing values, locale inconsistencies, and type issues Recommendations → Suggest improvements for data consistency 2. Data Transformations - Data Types → Convert strings to appropriate data types (date, datetime, number) - Locales & Units → Detect locales and unify currencies/units and formatting - Conversion → Convert all currency to USD and infer exchange rates by date Aggregation → Show totals by quarter 3. Sample Result - Sampling → Randomly sample 10% of rows emphasizing type repair and unit harmonization - Tracking → Include a column describing changes made Notes: - Use clear section headers and spacing - Bold text for outline level 1 and level 2 - All uppercase letters for outline level 1 - Include line spacing before each section level 1

Data Profiling

Profile the data. Evaluate and explain data completeness, uniqueness, drift and type inference

Prompt	Result
Task Overview: Flatted the data and rename columns with your best guess. Profile data (completeness, uniqueness, drift, type inference). Output Structure: Scorecards with icons and narrative recommendations. Notes: Include visual scorecards Ensure output uses plain text quotes (") without HTML entity encoding.

Stress Testing

Evaluate the performance impact of loading X MB of data into the current structure.

Prompt	Result
Task Overview: Explain how the report would perform if the data was 50 MB. Notes: - Ensure the output is clearly structured, with space between sections and subsections for readability. - Use icons to represent the estimated quality impact.

Why Use Narratives for Data Preparation?

Embedding Narratives in your Power BI report during the data preparation phase isn't just a technical exercise - it's a strategic tool for improving collaboration, confidence in data products and decision-making across all roles.

 

When narratives are used during data prep, they help teams:

• Pinpoint Where Data Cleansing Efforts Should Focus

  Narratives highlight inconsistencies, missing values, or schema issues uncovered during prep. This visibility ensures engineering resources are directed toward the areas that will have the greatest impact on downstream analytics.
• Identify When Data Quality Requires Review Before Moving Forward
  If narratives reveal anomalies-such as mismatched units, currency conversion gaps, or incomplete type repairs-it signals the need for a conversation with subject matter experts (SMEs) or stakeholders before publishing or modeling the data. They can also help analysts decide whether or not to request assistance from data engineers or anyone else that may be able to help with more complex transformations.
• Create a Transparent Audit Trail
  Documenting transformations like type conversions, locale detection, and harmonization in narrative form provides a clear record for governance and compliance teams, reducing ambiguity and manual reporting efforts.

 

How It's Useful for Different Audiences

• Analysts: Narratives validate assumptions, highlight where collaboration is needed on data quality and remediation if needed, confirm that data prep aligns with analytical goals, reducing rework later.
• Beginners: They provide an easy-to-understand explanation of complex transformations, making learning data prep concepts less intimidating.
• Business Users: Narratives translate technical steps into plain language, helping non-technical stakeholders understand what changed and why-before approving or acting on insights.

 

Final Thoughts: Narratives are a Data Prep Ally

By leverage Narratives throughout the data prep process, directly in a Power BI report, you turn your data prep process into a data exploration and documentation engine, and collaborative checkpoint - making it easier to validate assumptions, align with business rules, and avoid costly rework later in the pipeline.

Narratives are more than just storytelling tools - they are also a catalyst for intelligent automation. By thinking creatively, we can unlock new use cases that streamline data workflows and empower analysts to focus on what matters most: delivering insights.

If you're working with complex or messy data, consider using Narratives to do some of the heavy lifting. You might be surprised how much value they can add-outside the box.

 

Next Post Coming Soon

Power BI Narrative Prompt Frameworks and Ingredients - Powerful Tips and Tricks

Connect

Have you tried using Narratives in unconventional ways? I'd love to hear how you're pushing the boundaries of Power BI!

jennratten | Microsoft Fabric Community

Jennifer Ratten | LinkedIn

jennratten | github

jennratten | reddit

[johncagle]

@jennratten what a great idea!

[Manasa_Savanur]

Thanks for structuring this so well. I recently learned this the hard way after doing way too much rework.

[krishnakanth240]

Creative and insightful write-up @jennratten . Thanks for sharing.