• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course โ€“ Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Extract Start and End Dates with Power Query

You are here: Home / Power Query / Extract Start and End Dates with Power Query
extract start and end dates with power query
November 28, 2019 by Mynda Treacy

A few weeks ago, Matt asked if we could extract start and end dates with Power Query. He has a list of non-contiguous dates and wants to identify the various date ranges.

Taking the list below, you can see there are gaps indicated by the orange arrows (note, my dates are formatted dd/mm/yyyy):

Extract Start and End Dates with Power Query

The desired result is a table containing the start and end dates as dictated by the gaps in the list above:

start and end dates

Iโ€™m going to cover two ways we can tackle this, one method requires few steps, but it may suffer performance issues on large tables, the other will be more efficient with bigger lists, but requires more steps. Iโ€™ll cover both in this tutorial and Iโ€™m intentionally keeping this simple for those new to Power Query. Iโ€™m sure there are more complex approaches, but I like to use the GUI where possible because itโ€™s easier to remember.

Download Workbook

The workbook includes both query options.

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook and follow along. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.

Watch the Video

Subscribe YouTube

 

Written Instructions

Method 1 - Extract Start and End Dates with Power Query

Step 1: Get data from Excel Table

Data tab > From Table

step 1 get data from excel table

Step 2: Add Index column starting at zero

Power Query Editor Add Column tab > Index > From 0

step 2 add index columns starting at zero

Step 3: Add a Custom Column with a logical test

Reference the row below to check if itโ€™s the next consecutive date:

step 3 add a custom column

In English, this formula says; try checking to see if the date in the list column +1 is equal to the date in the List column on the current row +1, otherwise return the date from the List column.

The โ€˜tryโ€™ clause returns TRUE or FALSE. If the date on the next row is not 1 day after the current rowโ€™s date it will return FALSE. The โ€˜otherwiseโ€™ clause is used for the last date in List because there is no date after that row for the try clause to test.

Note: This referencing of rows can slow down performance over large data sets, therefore you may prefer method 2.

Step 4: Add End Date Column

Add a custom column with an if statement that extracts the date from the List column where the Custom column contains FALSE:

step 4 add end date column

In English this formula says; if the value in the Custom column is FALSE or itโ€™s data type is โ€˜dateโ€™ then return the date in the List column, else return null. We need the Value.Is function because the last row contains the final end date, so we want to include that in the End Date column.

Step 5: Fill Up End Dates

Select End Date column > Transform tab > Fill Up

step 5 fill up end dates

Step 6: Remove Duplicates from End Date Column

Select the End Date column > Home tab > Remove Rows > Remove Duplicates:

step 6 remove duplicates

Step 7: Delete Index and Custom Columns

Select the column headers > press the Delete key

Step 8: Rename Columns and Change Type

Finally, rename the columns โ€˜Start Dateโ€™ and โ€˜End Dateโ€™ and set the data type to Date.

You should be left with this:

step 8 rename columns

I named this query โ€˜Start and End Dates 1โ€™ as itโ€™s the first method.

Method 2 - Extract Start and End Dates with Power Query

Step 1: Get data from Excel Table

Data tab > From Table

step 1 get data from excel table

Step 2: Find Earliest Date

Duplicate the query: right-click query name > Duplicate. Transform tab > Date > Earliest

step 2 find earliest date

Rename the Query: MinDate

Step 3: Repeat for the Latest Date

Repeat steps 2 and 3 to find the Max Date.

Step 4: Add a day to MaxDate

Wrap the Date.AddDays function around the List.Max formula to add a day to the Max Date:

step 4 add a day to MaxDate

You should now have 3 queries; the original query that contains the table imported into Power Query, plus one for the MinDate and one for MaxDate:

3 queries

Step 5: Generate a Consecutive List of Dates

Create a new blank query:

In the blank query create a list of the dates from Min Date to Max Date

step 5 generate a consecutive list of dates

This returns a list of the date serial numbers. Weโ€™ll convert them to dates in a moment.

Step 6: Convert to Table

Convert the list of dates to a table. Transform > To Table:

step 6 convert to table

Notice you will now have a fourth query called Dates.

Step 7: Change Type and Rename Column

Set the data type to โ€˜Dateโ€™ and rename the column โ€˜Datesโ€™:

step 7 change type and rename column

Step 8: Merge Queries

Home tab > Merge Queries > As New

step 8 merge queries

This opens the Merge dialog box where you select the Dates query and the List query

select dates query and list query

Step 9: Expand the List Table

step 9 expand the list table

Step 10: Sort Rows

Sort by the Dates column in ascending order

step 10 sort rows

Step 11: Extract End Dates

Add a Custom Column with an if statement to extract the end dates:

step 11 extract end dates

In English, the formula reads; if the value in List.1 is null then return the date from the Dates column minus 1 day, else return null.

Step 12: Fill Up the End Dates

step 12 fill up the end dates

Step 13: Filter Out the null Values

step 13 filter out the null values

Step 14: Delete List.1 Column

Select the List.1 column > press Delete

Step 15: Group the End Dates

Home tab > Group By:

step 15 group the end dates

Step 16: Index the Grouped Rows

Add a custom column that numbers the dates in the Count columnโ€™s tables:

step 16 index the grouped rows

In English the formula reads; Add an index number column called โ€œDate Numberโ€ to the tables in the Count column, starting at 1 and incrementing by 1.

Step 17: Expand the Custom Column

step 17 expand the custom column

Step 18: Delete the โ€˜Countโ€™ Column

Select the โ€˜Countโ€™ column header and press the Delete key

Step 19: Filter the Date Number

Retaining only date number 1:

step 19 filter the date number

Step 20: Reorder Columns and Rename

Rename the โ€˜Datesโ€™ column to โ€˜Start Dateโ€™ and move to the front.

Step 21: Change Data Types

Set the Data Type for the columns to โ€˜Dateโ€™:

step 21 change data types

Iโ€™ve named this final query โ€˜Start and End Dates 2โ€™ as itโ€™s the second method.

In the Excel file available to download, youโ€™ll see Iโ€™ve placed the queries into folders for Method 1 and Method 2. The โ€˜Other Queriesโ€™ folder is empty:

queries method 1 and 2

Takeaway

While method 2 has a lot of steps, when working with large data sets itโ€™s likely to be the more efficient query. Therefore, itโ€™s important to keep in mind that lots of steps doesnโ€™t necessarily equal a slow query.

Thanks

A big thank you to Catalin who works with me for his contribution to method 1.

extract start and end dates with power query

More Power Query Posts

Power Query if Statements incl. Nested ifs, if or, if and

How to write Power Query if statements, including nested if, โ€˜if orโ€™ and โ€˜if andโ€™, which are easier to write than their Excel counterparts.
power query variables

Power Query Variables 3 Ways

Power Query Variables enable you to create parameters that can be used repeatedly and theyโ€™re easily updated as theyโ€™re stored in one place.
delete empty rows and columns using power query

Remove Blank Rows and Columns from Tables in Power Query

Delete blank rows and columns from tables using Power Query. Even rows/columns with spaces, empty strings or non-printing whitespace
extracting data from lists and records in power query

Extracting Data from Nested Lists and Records in Power Query

Learn how to extract data from lists and records in Power Query, including examples where these data structures are nested inside each other.
combine files with different column names in power query

Combine Files With Different Column Names in Power Query

Learn how to load data into Power Query when the column names in your data don't match up. Sampe files to download.
power query keyboard shortcuts

Power Query Keyboard Shortcuts to Save Time

Time saving keyboard shortcuts for Power Query that work in both Excel and Power BI. Download the free Shortcuts eBook
remove text between delimiters power query

Remove Text Between Delimiters – Power Query

Remove all occurrences of text between delimiters. There's no in-built Power Query function to do this, but this code does.
power query advanced editor tips

Tips for Using The Power Query Advanced Editor

Tips for using the Power Query Advanced Editor in Excel and Power BI. Watch the video to see these tips in action
pivot unknown variable number of rows to columns

Pivot an Unknown Number of Rows into Columns

How do you pivot rows to columns when you don't know how many rows you're dealing with? It's not as easy as you may think.
try otherwise power query iferror

IFERROR in Power Query Using TRY OTHERWISE

Using TRY..OTHERWISE in Power Query Replicates Excel's IFERROR So You Can Trap and Manage Errors In Your Queries.


Category: Power Query
Previous Post:Excel Small Multiple ChartsExcel Small Multiple Charts
Next Post:Excel XOR Function

Reader Interactions

Comments

  1. DANIEL OLIVES

    February 8, 2023 at 3:52 am

    Bonjour, malgrรฉ la correction du fichier excel , j’ai un message d’erreur
    Report Dates
    Chargement bloquรฉ par des รฉchecs avec d’autres requรชtes

    Multi-Date Check Results
    La table externe n’est pas dans le format attendu
    Daniel

    Reply
    • Mynda Treacy

      February 8, 2023 at 6:39 am

      Hi Daniel,

      Please post your question on our Excel forum where you can also upload a sample file and we can help you further.

      Mynda

      Reply
  2. aziz ur rehman

    August 4, 2020 at 6:09 am

    how to get time earliest or latest instead of date

    Reply
    • Mynda Treacy

      August 4, 2020 at 1:10 pm

      Hi Aziz,

      Duplicate the date column > select the duplicated date column > Transform tab > Date > Earliest. Repeat for Latest date.

      Mynda

      Reply
  3. Chris Yap

    July 2, 2020 at 6:50 pm

    Hi Mynda

    For step 4 of method 1, the End date formula
    = if [Custom] = false or Value.Is([Custom], type date) then [List] else null

    Value.Is([Custom], type date) for the last date in List is still not date, and alpha-numeric cannot change to Date for custom column (consist of True, False and the last end date), so how to make the last date return to End date column, can I use

    – if [Custom] = false or ([Custom] true and [Custom] false) then [List] else null

    Thank you !

    Reply
    • Mynda Treacy

      July 2, 2020 at 8:00 pm

      Hi Chris, I’m not following sorry. Please post your question on the forum so you can share a file and I can see what you’re referring to. Mynda

      Reply
  4. Kolyu

    November 29, 2019 at 2:32 am

    Hi Mynda,

    Nice tutorial but I think Method1 could be optimized.
    The slow work on large data sets is because of calling value from the next row.

    Instead of that we may add Index from 1 and then merge the query with itself using as a key in the first table the Index from 1 and in the second – Index from 0.

    After that it is easy. Here is my code

    let
    Source = Excel.CurrentWorkbook(){[Name=”List”]}[Content],
    #”Changed Type” = Table.TransformColumnTypes(Source,{{“List”, type date}}),
    #”Added Index” = Table.AddIndexColumn(#”Changed Type”, “Index”, 0, 1),
    #”Added Index1″ = Table.AddIndexColumn(#”Added Index”, “Index.1″, 1, 1),
    #”Merged Queries” = Table.NestedJoin(#”Added Index1″,{“Index.1″},#”Added Index1”,{“Index”},”Added Index1″,JoinKind.LeftOuter),
    #”Expanded Added Index1″ = Table.ExpandTableColumn(#”Merged Queries”, “Added Index1”, {“List”}, {“List.1″}),
    #”Added Custom” = Table.AddColumn(#”Expanded Added Index1″, “Custom”, each try if Date.AddDays([List],1)=[List.1] then null else [List] otherwise [List]),
    #”Filled Up” = Table.FillUp(#”Added Custom”,{“Custom”}),
    #”Removed Duplicates” = Table.Distinct(#”Filled Up”, {“Custom”}),
    #”Removed Columns” = Table.RemoveColumns(#”Removed Duplicates”,{“Index”, “Index.1”, “List.1″}),
    #”Renamed Columns” = Table.RenameColumns(#”Removed Columns”,{{“List”, “Start date”}, {“Custom”, “End Date”}}),
    #”Changed Type1″ = Table.TransformColumnTypes(#”Renamed Columns”,{{“End Date”, type date}})
    in
    #”Changed Type1″

    P.S. As usually Bill Szysz gives a nice and clean solution

    Reply
    • Mynda Treacy

      November 29, 2019 at 5:04 pm

      Nice, thanks for sharing, Kolyu! So many ways to achieve the same result with Power Query. I love all the different approaches.

      Reply
  5. Bill Szysz

    November 28, 2019 at 7:58 pm

    There is nothing wrong with your methods but there is a much easier way to do it.
    Check my comment below your video on YT.
    Greetings :-))

    Reply
    • Mynda Treacy

      November 28, 2019 at 10:32 pm

      Genius, Bill! Simply brilliant. Thanks for sharing ๐Ÿ™‚ Mynda

      For those interested, here is Bill’s solution:

      let
          Source = Excel.CurrentWorkbook(){[Name="List"]}[Content],
          #"Changed Type" = Table.TransformColumnTypes(Source,{{"List", type date}}),
          #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
          #"Changed Type1" = Table.TransformColumnTypes(#"Added Index",{{"Index", type duration}}),
          #"Added Custom" = Table.AddColumn(#"Changed Type1", "Diff", each [List]-[Index]),
          #"Grouped Rows" = Table.Group(#"Added Custom", {"Diff"}, {{"Start Date", each List.Min([List]), type date}, {"End Date", each List.Max([List]), type date}}),
          #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Diff"})
      in
          #"Removed Columns"
      Reply
  6. Vijayaraghavan

    November 28, 2019 at 4:06 pm

    Thank you for this tutorial. Difficult to follow the instructions in the video. But the tutorial is very handy. After reading the tutorial, the video becomes very easy to follow. Great job.

    Reply
    • Mynda Treacy

      November 28, 2019 at 4:27 pm

      Glad you found it helpful ๐Ÿ™‚

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

239 Excel Keyboard Shortcuts

Download Free PDF

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 ยท My Online Training Hub ยท All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.