▲Wirig Diagram Hot
▲ ▲ ▼

Power Query: Mastering Date Filters Before

By

Ever found yourself sifting through dates in Excel, wishing you could Carrie Bradshaw your data and make it do what you want? Enter Power Query, Excel's hidden gem, ready to transform your date wrangling into a breeze. Today, we're diving into Power Query before date transformations - the be-all and end-all of date manipulation.

But first, let's dip our toes into the power of Power Query. This add-in, available in Excel 2016 and later, turns your spreadsheet into a data hero, embracing the 'garbage in, garbage out' concept and turning it on its head. It's your secret weapon to clean, transform, and reload your data like a pro.

Power Query Basics: What & Why

Power Query is like having a data scientist roommate - always there, always ready to lend a hand (or a formula). It's a collection of tools that recycle your data, bring in new data, and merge them into one neat, navigable sheet.

How to Use Power Query Date Functions in Excel (3 Examples) - ExcelDemy
How to Use Power Query Date Functions in Excel (3 Examples) - ExcelDemy
Learn Power Query in Less than 10 Minutes
Learn Power Query in Less than 10 Minutes
POWER QUERY
POWER QUERY

But why Power Query before dates? Because dates are as finicky as a toddler on a sugar high. They can be formatted strangely, treated like numbers, or just plain ignored. It's our job, as data sorters, to tame the date wilds.

Understanding Date/Time Column Types

Before we dive in, let's understand the date-and-time column types in Excel. There are two - 'Date/Time' and 'Date'. 'Date/Time' stores both date and time data; 'Date' stores just the date. Knowing this can save you from a lot of formatting headaches.

Power BI Power Query Basics 📊 | Clean & Transform Data Like a Pro
Power BI Power Query Basics 📊 | Clean & Transform Data Like a Pro
Power Query: Get Today’s Date
Power Query: Get Today’s Date
Power Query: Date Difference
Power Query: Date Difference

Imagine you're working with a dataset where dates are stored as text. Power Query is your casually dressed savior, stepping in to make a quick date transformation, no tie required.

Converting Text to Date

So, you've got a column full of dates masquerading as text. Don't lose your cool (yet). Power Query can convert thoseText to date formats with a couple of clicks. Here's how:

  • Select the column. Click on 'Data Type' > 'Detect Data Type'. If it's not detected, select 'Date'.
  • In the 'Transform' tab, click on 'Date Column' and then 'Convert to Date'.
  • In the 'Create Date' dialog box, select the 'Date format' that matches your column, and click 'OK'.

And just like that, your dates are out of disguise. But what if you want to manipulate those dates in a particular way?

| Johannes Swanepoel
| Johannes Swanepoel
Power Query vs Power Pivot: What’s the Difference?
Power Query vs Power Pivot: What’s the Difference?
the power query manual for automate data preparation, with text and diagrams on it
the power query manual for automate data preparation, with text and diagrams on it
50 Things You Can Do With Excel Power Query (Get & Transform)
50 Things You Can Do With Excel Power Query (Get & Transform)
Expanding Rows Using Power Query
Expanding Rows Using Power Query
the screenshote menu in microsoft's workbook editor, which is open for all users to view
the screenshote menu in microsoft's workbook editor, which is open for all users to view

Power Query Before Date Transformations

Now that your dates are playing nice, let's manipulate them. Power Query has an entire 'Add Column' > 'Custom Column' section dedicated to dates.

But before we get to the good stuff, we need to understand date functions in Power Query. Power Query uses PLX language (also known as M language), which is designed to be straightforward and expressive.

Date Functions in Power Query

Power Query has a host of built-in functions to manipulate dates. Let's look at a couple:

The Hidden Power Query Feature That Fixes Broken Column Errors Instantly
The Hidden Power Query Feature That Fixes Broken Column Errors Instantly
  • Date.StartOfMonth(): Returns the start date of the current month.
  • Date.EndOfMonth():Returns the end date of the current month.
  • Date.AddDays(q, n): Adds n days to the q date.

You can use these functions to create custom columns easily. For example, if you want to create a 'Last Day of Month' column, you could use Date.EndOfMonth().

Creating Custom Date Columns

Here's how to create a custom date column using PLX:

  1. Click 'Add Column' > 'Custom Column'.
  2. Name your column (e.g., 'Last Day of Month') and then click in the 'Custom column formula' field.
  3. If your date column is named 'Transaction Date', your formula would look like this: Date.EndOfMonth('Transaction Date').
  4. Click 'OK'.

And there you have it - a new custom date column with the last day of each month, ready for action.

So, whether you're converting text dates to date formats, or manipulating dates with functions, Power Query before date transformations can save you time and sanity. Happy data wrangling!

💬 Comments ↗ Share ★ Save
AD