Calculate the Number of Days Between Order Date and Ship Date in Fabric Dataflow Gen2
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 1005 08/08/2026 null 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: Missing Order Date Missing Ship Date 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.Building Your First Pipeline in Microsoft Fabric
Data rarely lives where you need it. More often than not, the first real challenge of any analytics project is simply getting information from one place to another, reliably and on a schedule. This is exactly where Microsoft Fabric pipelines shine. If you have worked with Azure Data Factory before, much of this will feel familiar, but Fabric brings everything together inside a single, unified workspace and adds a few welcome conveniences along the way. In this article, we will walk through creating your very first Fabric pipeline from the ground up. We will start by setting up the pipeline and exploring the activities available to us, then move on to scheduling and monitoring. Finally, we will build a complete, end-to-end data movement that copies a file from an Azure Data Lake Storage (ADLS) account into a lakehouse. By the end, you will have a working pipeline and a solid mental model of how the pieces fit together. Creating the Pipeline Begin by opening your Newsletter_Pipelines workspace. To create a new pipeline, click on New Item. Give your pipeline a name — in this case, we will call it Ingestion Data. Once you click OK, you are taken to the pipeline authoring canvas shown below. From here, we can start building the pipeline from scratch. Click on the activity you want to add — we will choose Copy Data. Next, click on Activities. Exploring Activities At the top of the canvas you will find the activities bar. Clicking the Activities tab reveals the most commonly used activities right away. If you need something beyond those, the three dots open up a much longer list of options. From here you can, for instance, connect to Databricks or trigger a notification. One of the most useful additions in Fabric is the Outlook activity, which lets you send an email directly from the pipeline. You can even post a notification to Microsoft Teams. These options were not available in Azure Data Factory, so for data engineers they are a genuinely handy way to keep stakeholders informed. Scheduling and Running the Pipeline Adding activities is only half the story — at some point you will want the pipeline to run automatically. To handle that, click the Run button at the top. From there you can run the pipeline immediately, or set up a schedule. Clicking the Schedule button opens the scheduling options, where you can define how and when the pipeline runs. If you would rather use a different kind of trigger — a storage-based trigger, for example — you can configure that here as well. And whenever you want to check on past executions, View Run History gives you a complete record of every run. On the right-hand side, a slider lets you zoom in and out of the canvas as needed. A quick orientation tip for anyone new to Data Factory and pipelines: clicking anywhere outside the activity box surfaces the properties for the overall pipeline configuration in the bottom panel, while clicking inside the box shows the settings specific to that individual activity. Your First End-to-End Data Movement Now it is time for a complete Fabric data movement. Our goal is to build a pipeline that moves data from an Azure Data Lake Storage account into a lakehouse. To see what we are working with, head back to the ADLS account and open the landing container. Inside, you will find the source files. Of the three files here, suppose we only want to move orders.csv from ADLS to the lakehouse. To do that, we will use the Data Factory pipeline we just started building. We already have a pipeline with a single Copy activity in place. The first thing worth doing is renaming that activity to something meaningful — for example, Transfer data from ADLS to a Lakehouse. Naming the activity this way is not mandatory, but it is good practice. A descriptive name makes the pipeline far easier for other developers to understand at a glance; they can tell what the activity does without having to open it and inspect the source and destination. Configuring the Source To move the data, click on the Source tab and set up a connection to the source. Selecting Browse all reveals the wide range of data sources Fabric supports. We want to connect to an Azure Data Lake Storage account, but if you choose View more you will see that Fabric can also connect to SharePoint, Salesforce, Oracle, FTP, SFTP, and many others. Since we only need Data Lake Storage, type Data Lake at the top, find Azure Data Lake Storage, and click it to open the connector. With the connector open, we need to supply the URL, and Fabric will then ask for authentication. The credentials can be an organizational account, a SAS token, or an account key. In this example we will use a SAS token to connect to the Azure Data Lake Storage account. To generate one, go back to the Data Lake Storage account and search for SAS, then select Shared access signature. On the SAS configuration screen, allow all resource types, grant all permissions, and set the expiry far enough out so that you can still access it later. Then click Generate SAS and connection string. You will receive both a connection string and a SAS token. For now, copy the connection string and return to Fabric. When Fabric asks whether you are creating a new connection, choose yes, and paste in the URL. Paste the URL carefully. The example shows a format like https://<storage-account>.dfs.core.windows.net. When you copy and paste, however, the value often comes through as a blob type rather than dfs. Simply change blob to dfs manually and it will work correctly. We want the path to point as far as the landing container, so type landing here. Finally, give the connection a name — we will call it ADLS connection. Since this is neither a private nor an on-premises network, no data gateway is required, so leave that blank. Under authentication type, choose Shared access signature. Now return to the Azure portal, copy the SAS token, come back to Fabric, and paste it in. To recap: we supplied the URL, changed blob to dfs, named the connection, and provided the SAS token. With that done, click Connect. The connection takes a moment to establish and will eventually succeed. To confirm everything is working, click Test connection — you should see that the connection is successful. Next, choose which file or folder to bring in using the file path. The connection is made, but now we browse to a specific file — in this case, orders.csv. Click Browse, open the landing container, select orders.csv, and click OK. Because the file is a CSV, set the file format to DelimitedText. If you need finer control, the Settings tab lets you adjust details such as the column delimiter (useful when the file is not comma-separated) and whether the first line is a header. For now, click OK and move on to the destination. Before moving on, you can confirm the data looks right using the Preview tab. Clicking Preview Data shows the incoming records, and in this case everything reads perfectly. Configuring the Destination Now switch to the Destination tab. As before, there is no existing connection to our lakehouse, so click Browse All. We want to connect to the lakehouse, so type lakehouse. Rather than selecting the New Fabric item option, we will use the OneLake Catalog. Click View More, and under it you will find the e-commerce catalog — exactly where we want our data to land. Select it to make the connection to Orders_lakehouse. With the connection in place, we are almost ready to run. Fabric asks whether to copy the data into the Tables folder or the Files folder; we will save to Files for now. When prompted for a file path, you can browse the folders available in the lakehouse. We will create a new path called copied via pipeline and copy the data there. You can also set the output file format by clicking through the options — we will keep the data in Parquet format. Select Parquet, and we are done. All that remains is to execute the pipeline so it pulls the data from ADLS and pushes it into the lakehouse. Running and Verifying the Pipeline Click the Run button, then choose Save The pipeline now starts running, and full execution takes a little while. The Output tab shows live details. Initially the run sits in a queued state, waiting for resources. Once resources are assigned, it moves into the in-progress state and then completes. You can watch it progress from queued, to in progress, to succeeded. Our pipeline has executed successfully. Clicking into the run details, we can see it copied 3,000 records from the source file into the lakehouse, which now also holds 3,000 records. You will notice that the data read size and data written size differ. That is because Parquet is a compressed format, so the data footprint shrinks once written. To confirm the data actually landed, navigate to the lakehouse, where you will see the new copied via pipeline folder. Open it, and the Parquet files are right there. Wrapping Up And that is how you copy data from an ADLS account into a lakehouse using Fabric pipelines. In just a handful of steps, we created a pipeline, explored the activities Fabric offers, set up scheduling and monitoring, and built a complete data movement from source to destination — verifying along the way that every record arrived safely. The real takeaway is how approachable this process has become. What once required stitching together separate tools now happens inside a single, cohesive workspace, complete with conveniences like Outlook and Teams notifications that simply were not available before. With this foundation in place, you are well positioned to build richer pipelines: chaining multiple activities, adding transformations, and orchestrating sophisticated, scheduled workflows. Your first pipeline is rarely your last, but it is the one that makes everything that follows feel possible.Site to Insight: Fabric’s New SharePoint Picker
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.Creating Azure Data Lake Storage (ADLS) in Azure: A Step-by-Step Guide
In modern data platforms, building efficient and reliable data pipelines is at the heart of every data engineering workflow. This is where Microsoft Fabric Data Factory comes into play. It provides a powerful, intuitive interface to design, orchestrate, and automate data movement across different systems. With its visual pipeline designer, rich set of activities, and built-in scheduling and monitoring capabilities, Data Factory enables data engineers to create scalable workflows—from simple data ingestion to complex transformations—without heavy coding. As we move from understanding the interface to building real solutions, the next essential step is preparing a robust data source. In this article, we will start by creating an Azure Data Lake Storage (ADLS) account in Azure, upload sample data, and lay the foundation for our upcoming data pipelines. We’ll begin by setting up an Azure Storage account, where we’ll upload some sample data that will later be used in our data pipeline. I’ll assume you already have access to a Microsoft Azure account. Start by opening a new browser tab and navigating to the Azure portal (portal.azure.com). From there, we’ll proceed to create the storage account within your Azure environment. To set up the storage account, simply search for “Storage” in the Azure portal, where you’ll find the Create option—go ahead and click it. You’ll then be prompted to select your subscription and define a new resource group. Next, provide a unique name for your storage account. You can leave the region and other settings as default if they suit your needs and proceed by clicking Next. To configure it as an Azure Data Lake Storage account, make sure to enable the Hierarchical Namespace option. After that, continue clicking Next through the remaining steps. In the final stage, you’ll see a summary page displaying all your selected configurations. Take a moment to review everything, and once you’re satisfied, click Create to proceed. The deployment may take a few moments to complete. Once the Azure Storage account is successfully created, navigate to the resource to continue. Once the storage account is ready, the next step is to create a container. Navigate to Data Lake Storage, add a new container, give it a name like Building Container, and then click Create. Within this container, we can now upload our sample data. Simply select the required files—such as orders, products, and customers—and click Upload to add them. That’s it—your data has been successfully uploaded to the Azure Data Lake Storage account. In the next article, we’ll build a pipeline to read this data from ADLS and load it into a Fabric Lakehouse.How to Build and Orchestrate a Scalable Data Pipeline in Microsoft Fabric? A Step-by-Step Guide
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.Getting On-Premises SQL Server Data to Microsoft Fabric Lakehouse
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.Unlocking the Power of Fabric Data Pipelines: A Guide for Power BI users - Part 1
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!Data Pipeline Storage Event Triggers (Preview) - Ignore Empty Files
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.
