[Power Query] How to Extract Only the Date from a String and Add it to a Column

Introduction

When processing data using Power Query, you often encounter situations where you want to “extract only a specific part of a string and display it in a new column.”

The data I faced contained strings like the following: Measurement Date :2022年10月31日 14:31

I wanted to extract only the “2022年10月31日” (Date) part from this and add it as a separate column. I researched how to handle this in Power Query and will share the steps below.

Goal

• Extract only the “Date part (e.g., 2022年10月31日)” from the string in Column1.
• Add the extracted result as a custom column named “YearMonthDay”.

Steps: Extracting a Substring and Adding it to a Custom Column

Below are the steps to extract “2022年10月31日” from the string “Measurement Date :2022年10月31日 14:31”.

1. Open Power Query Editor

First, get the data in Excel and open the Power Query Editor. If “Transform Sample File” is displayed in the sidebar, select that to proceed.

2. Keep Top Rows (Filter Target Rows)

If the target string is in a specific row (e.g., the 1st row), perform the following operation to extract only that row.

1. Click “Keep Rows” in the top menu.
2. Select “Keep Top Rows”.
3. Enter “1” to keep only the first row.

3. Select Only the Target Column

If there are multiple columns, keep only the target column (e.g., Column1).

1. Click “Choose Columns” in the top menu.
2. Check only the necessary column and click “OK”.

4. Add Custom Column to Extract Date

This is the main processing step. We will extract only “2022年10月31日” from the target column.

1. Click “Add Column” -> “Custom Column” in the top menu.
2. Enter “YearMonthDay” (or your preferred name) as the New column name.
3. Enter the following in the “Custom column formula”:

Text.Range([Column1], 6, 11)

This formula extracts 11 characters starting from the 6th character (offset 6) of Column1.

4. Click “OK” to apply the process.

Supplement: What is the Text.Range Function?

Text.Range(text, start, count) is a function that extracts a part of a text string.

• text: The target string (e.g., [Column1])
• start: The zero-based offset to start from (0 is the first character).
• count: How many characters to extract.

Summary

In Power Query, it is easy to extract a part of a string and add it as a custom column.

• Use Text.Range to extract a specific range from a string.
• Use Custom Columns to process and enhance original data.
• This is particularly useful for extracting values from log data or strings with complex formatting.

By utilizing Power Query’s flexible text processing functions, you can significantly reduce the effort required for data preprocessing. I hope this article serves as a reference for your work.