When working with sales and order data, one of the simplest—and most useful—business questions is:

How many days did it take to ship an order?

At first glance, this looks like a straightforward subtraction:

Ship Date − Order Date = Shipping Days

But when you're working in Microsoft Fabric Dataflow Gen2, there is a little more to it than simply subtracting two columns.

The good news is that Dataflow Gen2 uses the familiar Power Query experience, so you can perform this calculation directly during your data transformation process without writing SQL or DAX. Microsoft describes Dataflow Gen2 as a cloud-based data preparation and transformation service built around the Power Query experience.

Let's see how to do it.

The Business Scenario

Imagine we have an orders table containing the following columns as seen in the screenshot below:

 The business wants a new column called Shipping Days. This calculation can be performed directly inside the Dataflow Gen2 transformation layer.

Start with Your Dataflow Gen2

Open your Microsoft Fabric workspace and create or open a Dataflow Gen2.

Once you're inside the Power Query editor, bring your orders table into the dataflow.

Dataflow Gen2 allows you to connect to a wide range of data sources and transform the data before loading it into destinations such as a Lakehouse or Warehouse.

The first thing I would check is the data type of both date columns.

Make Sure Both Columns Are Actually Dates

This is a small step that can save you from a surprisingly frustrating error.

Select OrderDate and confirm that its data type is Date.

Do the same for ShipDate.

You don't want one column to be Date while the other is Text or Date/Time.

Power Query performs date arithmetic based on compatible date and time types. Microsoft notes that subtracting different date/time types—for example, a date from a datetime—can result in an error unless you explicitly convert the values.

So, before doing the calculation:

OrderDate → Date

ShipDate → Date

As seen in the previous screenshot, the Order Date and the Ship Date columns has date data type

The Simple Solution

Now add a Custom Column.

In the Power Query editor:

Add Column → Custom Column

Give the new column a name:

ShippingDays

Then enter:

[ShipDate] - [OrderDate]

Dataflow Gen2 allows specifying the desired data type before hitting OK. This is not available in Power Query of Power BI and Excel . For this demonstration, the Whole number data type is selected. Then, click OK

That's it.

Power Query subtracts the two dates and produces the result as seen below:

 What If Your Columns Are Date/Time?

This is another situation you'll encounter frequently in real projects.

Suppose your data looks like this:

OrderDate = 2026-08-01 10:30:00
ShipDate  = 2026-08-03 14:45:00

Subtracting them produces a duration that includes both days and time.

If your requirement is to calculate the exact elapsed time, that's useful.

But if the business question is simply:

"How many calendar days passed between the order and shipping dates?"

you may want to convert both columns to Date first.

For example:

Date.From([ShipDate]) - Date.From([OrderDate])

and then:

Duration.Days(
    Date.From([ShipDate]) - Date.From([OrderDate])
)

This removes the time component from the calculation.

What About Missing Dates?

Real-world data is rarely perfect.

You might have an order like:

Order ID Order Date Ship Date

Perhaps the order hasn't shipped yet.

If you blindly calculate the difference, you won't get a meaningful shipping duration.

Instead, you can add some business logic.

For example:

if [ShipDate] = null then null
else Duration.Days([ShipDate] - [OrderDate])

This leaves the Shipping Days value blank until the order has actually shipped.

You could also return a label such as "Not Shipped" depending on the reporting requirement.

What If the Ship Date Is Earlier Than the Order Date?

This is another useful data-quality check.

Imagine:

Order Date = 10/08/2026
Ship Date  = 08/08/2026

The calculation would produce a negative duration.

Technically, that's valid arithmetic. From a business perspective, however, it probably indicates a data-quality problem.

You could identify such records with:

if [ShipDate] < [OrderDate] then "Invalid Dates"
else "Valid"

Or incorporate the validation directly into your shipping-days calculation.

This is one of the advantages of doing the transformation in Dataflow Gen2: you're not merely calculating a metric; you're also able to clean and validate the data before it reaches downstream reporting.

Why Calculate This in Dataflow Gen2?

You might wonder:

Why not simply calculate Shipping Days in Power BI using DAX?

You certainly can.

But there is a strong architectural argument for performing the calculation during data preparation when the value is fundamentally a property of the source data.

If ShippingDays is required by multiple reports, calculating it once in the dataflow means downstream consumers don't all need to recreate the same logic.

For example:

Source System
      ↓
Dataflow Gen2
      ↓
Clean + Transform
      ↓
ShippingDays
      ↓
Lakehouse / Warehouse
      ↓
Power BI Semantic Model
      ↓
Reports

Now the transformation becomes part of your reusable data pipeline.

A More Production-Ready Example

For a real production dataflow, I might use something like:

if [OrderDate] = null or [ShipDate] = null then
    null
else if [ShipDate] < [OrderDate] then
    null
else
    Duration.Days(
        Date.From([ShipDate]) - Date.From([OrderDate])
    )

This handles three important scenarios:

1. Missing Order Date

2. Missing Ship Date

3. Ship Date earlier than Order Date

Only valid records receive a shipping duration.

You can then create a separate data-quality flag for the invalid records rather than silently hiding them.

Turning the Calculation into a Business KPI

Once you have ShippingDays, you can do much more than display it in a table.

For example, the business could define:

0–2 days   → Fast
3–5 days   → Standard
6+ days    → Delayed

That allows Power BI to answer questions such as:

• What is our average shipping time?

• Which customers experience the longest delays?

• Which products take the longest to ship?

• Which warehouses are performing poorly?

• What percentage of orders ship within two days?

• Has shipping performance improved over time?

A simple date subtraction has now become a useful operational metric.

The Bigger Fabric Lesson

This small example demonstrates an important principle in Microsoft Fabric:

Transform data as close to the data-engineering layer as practical.

Dataflow Gen2 gives you a low-code environment for ingestion and transformation using Power Query, while allowing the resulting data to be loaded into Fabric destinations for downstream analytics.

Instead of repeatedly calculating the same business logic inside individual Power BI reports, you can create a reusable transformation once and make the resulting column available to multiple consumers.

And because Dataflow Gen2 is part of the broader Fabric ecosystem, the transformation can become one stage of a much larger data platform.

In conclusion, calculating the number of days between Order Date and Ship Date in Dataflow Gen2 is straightforward once you understand how Power Query handles dates.

The key expression is:

Duration.Days([ShipDate] - [OrderDate])

But the real lesson goes beyond the formula.

Always check your data types, handle missing values, validate unexpected dates, and decide whether the calculation belongs in the transformation layer or the semantic model.

Sometimes a two-line Power Query expression is all you need to turn raw operational data into a useful business metric.

And that's one of the things I like about Microsoft Fabric—simple transformations can become reusable building blocks in a much larger analytics architecture.

One of the greatest things about attending FABCON in Atlanta recently wasn’t just the incredible community or the deep-dive sessions – it was those “aha!” moments during the keynote announcements. Among the many massive reveals, there was one specific update that caught my attention as a massive “quality of life” win: the SharePoint Site Picker (Preview).

For anyone who works between SharePoint and Fabric daily, this was easily one of my favourite announcements of the conference. Here’s why it’s a gamechanger.

If you are working with large fact tables in Fabric, loading everything on every run quickly becomes slow and costly. Incremental loading fixes that by moving only the new records since the last pipeline run, and the watermark pattern makes this easy to manage. In this walkthrough, you will see how to set up a simple watermark table, build a stored procedure, and wire up a Fabric pipeline that reads the last load timestamp, fetches the latest data from FactImport, copies only the delta, and updates the watermark for the next run. It is a straightforward pattern that keeps your Warehouse and Lakehouse in sync without wasting compute or time.

Power Query’s Table.TransformColumnTypes has long supported a Culture tag as an optional third parameter. As of May 2025, it now goes further: you can pass an Options record that includes both Culture and MissingField. This makes it the only M function where MissingField lives inside the Options record, bringing new flexibility and fewer errors.

DISCLAIMER: This method uses the internal takeout-pa.clients6.google.com API and is thus UNSUPPORTED and BRITTLE.

This is a custom function that you can paste into advanced editor and then use to get Public Google Drive contents with just the Drive ID and without needing a) any authentication or b) a Google Cloud Project key.

tl;dr List.Contains is slow in Power Query because for each iteration, for example each row in your table you check, it scans the list from the start until it finds a match. Buffering helps so it doesn’t re-evaluate on every iteration, but it still scans each item in the list. A better approach is merging via Table.Join, or best of all (in the right scenario), turn your list into a single record to create a hash map and do near constant-time lookups with Record.Fi eldOrDefault.

How to Build and Orchestrate a Scalable Data Pipeline in Microsoft Fabric? A Step-by-Step Guide

In today’s data-driven world, organizations handle massive volumes of information from multiple sources. The challenge is not just storing this data but ensuring it is organized, transformed, and analytics-ready—without manual intervention.

Auto-Magic Data Ingestion in Microsoft Fabric Lakehouse using Data Activator & Pipelines

Imagine this you drop a .csv file into your Lakehouse, and within moments - without any manual intervention - it appears as a fully formed Delta Table in your workspace. No scripts, no button clicks. Just pure automation.

Learn a handy Power BI trick to set column data types directly in the Power Query M formula. This approach avoids adding extra “Changed Type” steps and keeps your queries clean.

Ever wondered if you can send personalized emails directly from Microsoft Fabric Data pipelines?
In my latest blog, I show how I built a simple automation that notifies employees when their weekly working hours fall below a threshold — no Power Automate, no external tools.
All done within the Fabric ecosystem

Public parameters are two small words that substantially boost the versatility and usability of Dataflow Gen2s in Microsoft Fabric for your data orchestrations.

In this blog post, we'll explore how you can start using Fabric Data Pipelines as a Power BI user that wants to take full advantage of Microsoft Fabric. And if you have never used Power BI before but still want to start with pipelines, you are in the right place!

In this blog we discuss how to use the expression language to handle referencing a field that may or may not exist at runtime, a non-existent property. 

This short blog details a common scenario we saw in Azure Data Factory where we wanted to ignore zero-byte (empty) files landing in out storage accounts.  In this blog we show you how to achieve this functionality with data pipeline storage event triggers (preview) and provide references to the properties and schemas of the event grid topics which will empower you to specify the filters you need to be successful. 

For the last few days, I have been working on the Contoso Sales data to create a Power BI report as part of the learning. Currently, I am using the default ready to go Power BI data model provided by Microsoft which can be found here. As Microsoft Fabric is the new tech buzz, so I thought why don’t I get this data somehow in the Fabric environment.

In this post I want to cover my initial tests of the Data Factory Testing Framework. Which is a unit testing framework you can use to test Microsoft Fabric Data Pipelines.

I wanted to cover this framework since I mentioned it in a previous post about unit tests on Microsoft Fabric items.