Separate Date and Time with Power Query

Separate Date and Time with Power Query
Last Updated on 15/03/2024
Reading time: 3 minutes

Learn how to use Power Query to separate date and time into two columns for easier data analysis. The technique is different if you prefer to use Excel functions.

  1. Select your column with Date & Time

    Only one column must be selected

  2. Add a new column with only the Date

    There is one option in the Power Query ribbon to keep only the Date

  3. Add a new column with only the Time

    Same technique but this time for the Time

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 in your column is now "Date/Time" and the pictogram isn't the same

Type Date and Time in power query

Step 3: Extract Only the Date

Once your data type is Date and Time, it's very easy to separate the Date from the column with Power Query.

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

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.

You can gain deeper insights and make better-informed decisions by transforming your data into a more usable format.

Leave a Reply

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

Separate Date and Time with Power Query

Reading time: 3 minutes
Last Updated on 15/03/2024

Learn how to use Power Query to separate date and time into two columns for easier data analysis. The technique is different if you prefer to use Excel functions.

  1. Select your column with Date & Time

    Only one column must be selected

  2. Add a new column with only the Date

    There is one option in the Power Query ribbon to keep only the Date

  3. Add a new column with only the Time

    Same technique but this time for the Time

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 in your column is now "Date/Time" and the pictogram isn't the same

Type Date and Time in power query

Step 3: Extract Only the Date

Once your data type is Date and Time, it's very easy to separate the Date from the column with Power Query.

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

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.

You can gain deeper insights and make better-informed decisions by transforming your data into a more usable format.

Leave a Reply

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