Open In App

Date Modifications with Power Query in Excel

Last Updated: 20 Jan, 2023

C

Chetan
Summarize
Comments
Improve
Suggest changes
Like Article
Like
Share
Report
News Follow

A data preparation and transformation engine is Power Query. Power Query includes a Power Query Editor for implementing transformations as well as a graphical interface for obtaining data from sources. With Power Query (also referred to as Get & Transform in Excel), you may import or connect to external data and then modify that data to match your needs, for instance, by combining tables, altering the data type, or eliminating a column. When you’re ready to produce charts and reports, load your query into Excel. You may periodically update the data by refreshing it.

Steps to Open Power Query in Excel

Step 1: First create a table in Excel. 

creating-a-table

 

Step 2: Select the field and then navigate to the data tab on the top of the ribbon. Then select the Get data option and choose From other sources and select From Table/Range option.

navigating-data-tab

 

Step 3: Then Create Table dialog box appears. Here select the table range and then click OK.

create-table

 

Step 4: Now you can see it converts the excel table to a power query Editor.

table-converted-to-power-query-editor

 

Date modification with Power Query

Age

The number of days between the specified date and the current date is determined when you choose the Age option and choose a date column. Next, you must swap out the current date for the one in the second date column.

Step 1: Select the date column. Navigate to the add column tab on the top of the ribbon and then go to the Date and select Age.

selecting-date-column

 

Step 2: Now as you can see with this command, the Date value is transformed into the AgeFromDateTime based time format: Days.Hours:Minutes: Seconds or 2923.11:48:53.3504693.

command-shown

 

Date Only

When you choose the Date Only option and choose a date column, the time is removed from the display and only the date is displayed. It creates a new column with the date name and shows only the date.

Step 1: Select the date column. Navigate to the add column tab on the top of the ribbon and then go to the Date and select Date Only. 

navigating-add-column-tab

 

Step 2: Now as you can see with this command, the Date value is transformed into the Date-based format: Date or 12-01-2015.

date-value-transformed

 

Year

When you choose the Year option while selecting a date column, just the year is displayed and the date and time are not displayed. It adds a new column with the name of the year and displays only the year. There are several choices available: year, the start of the year, and the end of the year.

Step 1: Select the date column. Navigate to the add column tab on the top of the ribbon and then go to the Date and select Year.

selecting-date-and-select-year-option

 

Step 2: Now as you can see with this command, the Date value is transformed into the Year: Year or 2015.

date-value-transformed

 

Month

When you choose the month option while selecting a date column, just the month is displayed and the date and time are not displayed. It adds a new column with the name of the year and displays only the year. There are several choices available: month, the start of the month, the end of the month, the days in the month, and the name of the month.

Step 1: Select the date column. Navigate to the add column tab on the top of the ribbon and then go to the Date and select the Month option here we are choosing Days in a month.

choosing-days-in-a-month

 

Step 2: Now as you can see with this command, the Date value is transformed into the Days in Month: Days in every month or 31/30.

date-value-transformed

 

Quarter

When you choose the Quarter option while selecting a date column, just the Quarter is displayed. It adds a new column with the name of the quarter and displays only the quarter. There are several choices available: a quarter of the year, the Start of the quarter, and the End of the quarter.

Step 1: Select the date column. Navigate to the add column tab on the top of the ribbon and then go to the Date and select the Quarter option here we are choosing Quarter of Year.

choosing-quarter-of-year

 

Step 2: Now as you can see with this command, the Date value is transformed into the Quarter of the year: Quarter or 1.

date-value-transformed

 

Week

When you choose the week option while selecting a date column, just the week is displayed. It adds a new column with the name of the week and displays only the week. There are several choices available: The week of the year, the week of the month, the start of the week, and the end of the week.

Step 1: Select the date column. Navigate to the add column tab on the top of the ribbon and then go to the Date and select the Week option here we are choosing Week of year.

choosing-week-of-year

 

Step 2: Now as you can see with this command, the Date value is transformed into the Week of the Year: Week or 3.

date-value-transformed

 

Day

When you choose the day option while selecting a date column, then just the day is displayed. It adds a new column with the name of the day and displays only the day. There are several choices available: day, day of the week, day of the year, the start of the day, end of the day, and name of the day. 

Step 1: Select the date column. Navigate to the add column tab on the top of the ribbon and then go to the Date and select the Day option here we are choosing Name of Day.

choosing-name-of-day

 

Step 2: Now as you can see with this command, the Date value is transformed into the Name of the Day: Day Name or Monday/Tuesday.

date-value-transformed

 

Parse

When you choose the Parse option and choose a date column, the time is removed from the display and only the date is displayed. It creates a new column with the Parse name and shows only the date.

Step 1: First you need to convert the date to text then the parse function will get active.

activating-parse-function

 

Step 2: Then select the date column. Navigate to the add column tab on the top of the ribbon and then go to the Date and select the Parse.

selecting-parse

 

Step 3: Now as you can see with this command, the Date value is transformed into the Parse: Parse or 12-01-2015.

date-value-transformed

 

Subtract Days

When you choose the Subtract Days option and choose both date columns, then it subtracts both columns. It creates a new column with the subtraction name and shows the result of the subtraction.

Step 1: First select the two columns. Navigate to the add column tab on the top of the ribbon and then go to the Date and select the Subtract Days.

selecting-subtract-days

 

Step 2: Now as you can see with this command, subtract the two dates: Subtract Days or -169.

subtract-days-obtained

 

Combine Date and Time

When you choose the Combine Date and Time option and choose both date and time columns, then it combines both columns. It creates a new column with the merged name and shows the result of the combination.

Step 1: First select the two columns. Navigate to the add column tab on the top of the ribbon and then go to the Date and select the Combine Date and Time.

selecting-combine-date-and-time

 

Step 2: Now as you can see with this command, Combines the date and time column: Combine Date and Time or 12-01-2015 12:02:12.

date-and-time-column-combined

 

Earliest and Latest Days

Searches for the earliest or latest day. Since there is only one value as a result, it is useless for New Column and only useful for Transform.



Article Tags :

C

Chetan
News
Improve
Discuss
Do you want to advertise with us?Click here to know more

A

Ankur Pal

Text Modifications with Power Query in Excel

article_img
In Excel, Power Query is a business intelligence tool that is used to analyze the data imported from different sources. With the help of power query, we can easily clean, transform and reshape our data as per requirement along the way of analyzing the data. In this example, we will learn about the Text Functions which can be easily used with the help of Power BI for the modification of the data. Given below are the formatting functions that can be used to create and manipulate the text values. Function Name Description Trim It is used to trim(remove) the whitespace from both ends of the text.Clean It removes the non-printable characters from the text and returns it.Merge Columns It is used to combine two or more columns into a single column using a separator.Lowercase It is used to make text lowercase.Uppercase It is used to make text uppercase.Add Prefix It is used to add prefixes to a cell or set of cells.Add Suffix It is used to add suffixes to a cell or set of cells.Capitalize Each Word It is used to change a text into proper text.Text Modifications with Power Query Step 1: Create a Database. In order to use the text operations, we will be using the following random names.  Fig
Read More

R

Romy Kumari

How to Calculate End Date from Start Date and Duration in Excel?

Excel is a powerful data visualization and analysis software, which uses spreadsheets for managing, storing, and visualizing a large amount of data. A cell, a rectangular block is used to store each unit of data. This tool can be used to visualize trends or compare data by creating graphs, or get insights from the data using functions and formulas. It is primarily used for accounting purposes but can be used by anyone for many different tasks as per the requirements. Let's see how to calculate the end date from the start date and duration. Calculate the end date from the start date and duration (weeks) To calculate the end date from the start date and the number of weeks(duration), use the below formula: =IF(start_date, start_date+(duration_in_weeks*7),"") This formula will add (week * 7) days to the start date, as a week has 7 days. For purpose of demonstration use the below data to calculate the end date: Step 1: Apply the formula, Step 2: Drag the fill handle over to the data range. Step 3: To change the value into date format, click the Home tab, and choose Short Date from the General drop-down list. Output Calculate the end date from the start date and duration (mont
Read More

A

Asha Senthilkumar

Excel - Types of Merging of Queries in Power Query

article_img
In Power Query, we have an option to Combine two tables using “Merge queries”. Merge Queries: Merge with current query with another queryMerge Queries as New: Merge with current query with another query and create New query In this article, we explore different types of merging of queries in Power Query with two tables. We will combine two years of sales data (2020 and 2021) with the key field “Cust_ID”. Implementation: Follow the below steps to implement merging of queries: Step 1: Open Power Query Editor. Step 2: Select your Query “Sales_2020”. Go to Home >> Merge Queries >> Merge Queries as New. We have six type of joins: Left Outer (all from first, matching from second)Right Outer (all from second, matching from first)Full Outer (all rows from both)Inner (Only Matching rows)Left Anti (rows only in first)Right Anti (rows only in second) Step 3: Select Second table; Select key fields “Cust_ID” in both tables; Select Left Outer (all from first, matching from second) and Press “OK”. Step 4: Above step create a new query “Merge 1” as below in your power query. Note: Sales_2021 is not a text value it is a table for each row. Step 5: Click “Sales_2021” drop down icon; M
Read More

G

Gautam goel

How to Create a Relative File Path with Power Query in Excel?

article_img
In today's digital environment, sharing Excel files effectively is vital. A common issue is that Excel's Power Query uses absolute file paths, which can break when files are opened on different systems. To enhance portability and maintain data connections, it's crucial to convert these absolute paths to relative paths. This article explains how to make Excel workbooks more shareable by using Power Query to switch from absolute to relative file paths. How to Create a Relative File Path with Power Query in Excel?Why Convert Absolute Paths to Relative Paths?Problem with Absolute Path: Absolute paths can cause issues when sharing files because the path is specific to the original user's computer. Due, to this file sharing, is a big and significant issue. For example, an analyst is analyzing data in its intelligence tools source via power query from your PC, the tools work well until the files are in your PC, but stop working as soon as they are imported into the third person PC, this happens because the absolute path in the third person computer might be different, and our power query files are still searching for the old absolute path provided in my computer. To resolve this issue, on
Read More

C

Chetan

How to Add a Conditional Column in Power Query in Excel?

We may use Power Query to generate new columns whose values depend on one or more criteria that have been applied to existing columns in your database. The condition imposed on an existing column in the data model serves as the foundation for Power BI's conditional column. On the Add column tab, in the General category, is the Conditional column command. In this article, we are going to discuss the Conditional column in Power Query and in Power Pivot queries. Step By Step Implementation Step 1: Create an excel table with fields Roll No., Student name, and Marks. Step 2: Select a table. Then navigate to the data tab on the top of the ribbon and select the Get Data option and then go to from other sources and choose from Table/range. Step 3: Then create a Table dialog box appear. Here select the range and check my table has headers then click on OK. Step 4: Now as you can see the excel table is converted to a power query editor. Step 5: Now in the power query go to the add column tab on the top of the ribbon and here select the conditional column. Step 6: Add Conditional Column dialog box appears. Here write the new column name and then apply the if condition. Step 7: Her
Read More

D

Dhatri

How to Import, Edit, Load and Consolidate Data in Excel Power Query?

article_img
Power Query is an easy and efficient way of solving simple data tasks. Most of our valuable time is frequently consumed by tedious manual procedures like cut and paste, column merging, and filtering. These operations are greatly simplified with the Power Query tool. A further advantage is that, in comparison to other BI tools, Power Query is simple to use. The Power Query user interface is straightforward. Given how much it resembles the Excel interface, many users will find it to be familiar. What is Power Query? Power Query is an application for preparing and transforming data. Using Power Query, you may perform transformations to data obtained from sources using a Power Query Editor and a graphical user interface.  We can import data from a variety of sources, clean it, convert it, and then reshape it to suit our needs using Power Query, a business intelligence tool provided by Microsoft Excel. Power Query allows us to create a query just once and reuse it later by simply refreshing it. Different Ways to Import Data to the Excel Sheet Excel allows importing data from a variety of sources. We will look at each of these in detail. While importing data, power query has several feat
Read More

C

Chinmay Mundhada

How to use Power Query as a Pivot Table Data Source?

article_img
Power Query is a tool that is available in Microsoft Excel that simplifies the process of importing data from different sources and sorting them into an Excel sheet in the most convenient and usable format. It is a tool in Microsoft Excel that is used to simplify the process of importing data from different source files and sorting them into an Excel sheet. It also removes the need to do the same filtering techniques to transform the same data set at different points as per requirement; the user only needs to set up a query, that is, the rules for sorting, once, and refresh the query every time you need to perform a particular action. So, you can easily use a power query once and simplify your data rather than using it again and again. The biggest advantage you get when you use the Power Query is higher efficiency. A pivot table is aggregated value such as the sum, average, minimum, and maximum of a large dataset to represent the dataset in a concise manner. Pivot tables are generally used to represent the Summary of large datasets. The input of Power Query effectively helps to put large datasets in the proper format for the pivot tables to process data. Steps to implement Power Qu
Read More

R

Rohit Sarkar

How to Merge Content of All Files in Folder with Power Query?

article_img
Use Power Query to create a single table from numerous files with the same schema that are saved in the same folder. For instance, you could wish to merge budget workbooks from several departments each month when the columns are the same but the workbooks have different numbers of rows and values. Once it is configured, you may add further transformations, just as you would with any other single imported data source, and then refresh the data to view the results for each month. Merging Content of All Files Suppose we have multiple files in a folder that contains consistent types of data, with the help of power query we can merge all the data together with the help of power query easily. Let's say there is a folder "Student" that contains several files that contain information about the students in the form of a table. We need to merge all the student details from all the files into a single file. As far as all the files are consistent, which means hold similar kind of tables only, merging all the details are very easy. Steps to Merge Content of All Files in Folder with Power Query Step 1: Open Excel and then go to the Power Query on the top of the ribbon and then select From File
Read More

R

RISHAB NIGAM

Power Query – Source Reference as File Path in Cell

article_img
Power query helps in doing automation in an efficient manner. It allows users to utilize files stored in specific locations and apply routine transformation steps on those files. It allows users to embed file paths and file sources in an Excel cell. The end user can make use of named ranges and Excel cells as references to access multiple files. In this article, we will learn how to refer to Source as a file path in a cell.  Adding Source Reference as File Path in Cell Let us take a basic example of how to make use of Excel cells in manipulating file paths. Below is an example where 2 Excel files are there, Power query1.xlsx and Power query2.xlsx.  The reference to source as file path, is only valid if the excel files, have same table name, and same number of columns in it. For example, Power query1.xlsx and Power query2.xlsx, has same table name(Table_src) and same number of columns.  Step 1: In the above example, Excel cells B2 and B3 contain the path of the Excel file. Let us bring the data to be used for transformation. The name of cell B2, should be replaced with FileP, as shown in the image below. FileP will be the name range cell of B2.  Step 2: To do this, go to the Data ri
Read More

N

Naveen Kumar Kharwal

How to Sort by Date in Excel?

article_img
Excel provides various types of sorting formats e.g. “sort A to Z”, “Sort Z to A”, “Sort by color” and “Sort by date” etc. Sorting in Excel is nothing but arranging data into some meaningful order to make it easier to understand, analyze, or visualize. Here, we will be discussing how to sort by Date in Excel whether it may be Years, Months, Days, or weeks. How to Sort by Date in Excel? How to Sort by Date in Excel?Chronological or reverse chronological order is the easiest way to sort data by date. This sorts the data in ascending or descending order. In Excel, you must have the dates in one single format to sort the data by date otherwise they do not always work. To sort the dates in chronological order follow the below steps: Step 1:  Select the dates which you want to sort in ascending or descending order. Step 2: Click Home tab -> Click on the arrow under Sort & Filter, and then click Sort Oldest to Newest. As a result, data is sorted from oldest to newest. Step 3:  If you want to sort dates from Newest to Oldest, then Click Home tab -> Click on the arrow under Sort & Filter, and then click Sort Newest to Oldest. As a result, data is sorted by newest to oldest. Ho
Read More
three90RightbarBannerImg
We use cookies to ensure you have the best browsing experience on our website. By using our site, you acknowledge that you have read and understood our Cookie Policy & Privacy Policy
Lightbox