Extract Start and End Dates with Power Query - EBOOK VBA EXCEL

EBOOK VBA EXCEL

EVBA.info ( Ebook VBA Excel ) Welcome EVBA.info - Nothing Is Unable ... About Excel Tricks, Learning VBA Programming, Dedicated Software, Free Courses, Living Skills ...

Friday, May 22, 2020

Extract Start and End Dates with Power Query

Extract Start and End Dates with Power Query

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.
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.

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

#evba #etipfree #eama #kingexcel
📤How to Download ebooks: https://www.evba.info/2020/02/instructions-for-downloading-documents.html?m=1

No comments:

Post a Comment