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.
- Select your column with Date & Time
Only one column must be selected
- Add a new column with only the Date
There is one option in the Power Query ribbon to keep only the Date
- 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
And then, in Power Query, you can visualize your data in Power Query
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.
It's compulsory to change the type of your data to Date & Time
And then, the type of the data in your column is now "Date/Time" and the pictogram isn't the same
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.
- Tab Add Column
- Then Date
- And finally, Date Only
The result is amazing: a new column with only the date
Step 4: Extract Time
Here, the technique is as simple as the previous step
- Select the column with Date and Time first
- Then, select the tab Add Column
- And Sub-Menu Time
- Finally, Time Only
And now, you have another column with the Time only
Conclusion
Using Power Query to split date and time values can greatly improve your data analysis and visualization in a Power BI project. Transforming your data into a more usable format can help you gain deeper insights and make better-informed decisions.
