Power Query Split Date and Time

Power Query Split Date and Time
Last Updated on 18/05/2023
Reading time: 2 minutes

Learn how to use Power Query to split date and time values into separate columns for easier data analysis.

This step-by-step guide will walk you through the process of transforming your date time data into a more usable format, saving you time and improving your data accuracy. If you prefer to use Excel functions, the technique is different.

Step 1: Load your dates to Power Query

Once your data are in a Table, you can load them to Power Query with the menu Data > From Table / Range

Menu Data From Table Range

And then, in Power Query, you can visualize your data in Power Query

Load date and time data to Power Query from a Table

Step 2: Change the type of your data

As you can see on the header, you have the symbol ABC/123. That means Power Query considers your data as "Any"; not a text, not a date, not a number.

Type Any in Power Query

It's compulsory to change the type of your data to Date & Time

Power Query Change Type to Date and Time

And then, the type of the data of your column is now "Date/Time" and the pictogram isn't the same

Type Date and Time in power query

Step 3: Extract Date

Once the type of your data is Date and Time, it's very easy to extract the Date.

  1. Tab Add Column
  2. Then Date
  3. And finally Date Only
Split Date from Date and Time with Power Query

And the result is amazing; a new column with only the date

Date only extracted

Step 4: Extract Time

Here, the technique is as simple as the previous step

  1. Select the column with Date and Time first
  2. Then, select the tab Add Column
  3. And Sub-Menu Time
  4. Finally, Time Only
Power Query Extract Time only

And now, you have another column with the Time only

Time extracted only

Conclusion

Using Power Query to split date and time values can greatly improve your data analysis and visualization in a Power BI project.

By transforming your data into a more usable format, you can gain deeper insights and make better-informed decisions.

Leave a Reply

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

Power Query Split Date and Time

Reading time: 2 minutes
Last Updated on 18/05/2023

Learn how to use Power Query to split date and time values into separate columns for easier data analysis.

This step-by-step guide will walk you through the process of transforming your date time data into a more usable format, saving you time and improving your data accuracy. If you prefer to use Excel functions, the technique is different.

Step 1: Load your dates to Power Query

Once your data are in a Table, you can load them to Power Query with the menu Data > From Table / Range

Menu Data From Table Range

And then, in Power Query, you can visualize your data in Power Query

Load date and time data to Power Query from a Table

Step 2: Change the type of your data

As you can see on the header, you have the symbol ABC/123. That means Power Query considers your data as "Any"; not a text, not a date, not a number.

Type Any in Power Query

It's compulsory to change the type of your data to Date & Time

Power Query Change Type to Date and Time

And then, the type of the data of your column is now "Date/Time" and the pictogram isn't the same

Type Date and Time in power query

Step 3: Extract Date

Once the type of your data is Date and Time, it's very easy to extract the Date.

  1. Tab Add Column
  2. Then Date
  3. And finally Date Only
Split Date from Date and Time with Power Query

And the result is amazing; a new column with only the date

Date only extracted

Step 4: Extract Time

Here, the technique is as simple as the previous step

  1. Select the column with Date and Time first
  2. Then, select the tab Add Column
  3. And Sub-Menu Time
  4. Finally, Time Only
Power Query Extract Time only

And now, you have another column with the Time only

Time extracted only

Conclusion

Using Power Query to split date and time values can greatly improve your data analysis and visualization in a Power BI project.

By transforming your data into a more usable format, you can gain deeper insights and make better-informed decisions.

Leave a Reply

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