Here’s a question from Marla:
I have imported a table from Excel. When i bring it up in Power BI , dates are displayed in long form when i actually need the traditional mm/dd/yyyy format – basically get rid of the time values.
Transform Date/Time to short dates in Power BI
Proceed as following to change your datetime column format to date formats such as mm/dd/yyyy or dd/mm/yy:
- Open Your Power BI desktop report.
- From the Data pane at your right hand side pick your Date column in long format.
- Then right from the Ribbon, modify the the Data type from Date/time to Date.
- Then, adjust the format as needed to Short Date or other more specific formats such ad dd/mm/yyyy or others.
Before converting to Short Dates:
After converting to Short Dates:
Split Date / time values in Power BI
To extract the time values into a new column, proceed as following:
- In Power BI Desktop Data Panel select your table.
- Hit the New Column button.
- Then use the following DAX statement to extract your time values from your date/times.
Time only = FORMAT('Dates Tutorial Text'[Date],"hh:mm")- This will create a new column named Time Only in your table showing your time values in hh:mm (hour and minutes) format.
Convert date times to dates with Power Query
Whenever possible, it is advisable to manipulate your data during the data preparation phase. Therefore, for completeness i would like to highlight the step-by-step instructions for data conversion using Power Query.
Proceed as following to convert your DateTime values to a Date in Power Query:
- First off, start by loading your data into Power Query from your data source (Excel, CSV, an SQL database etc.). If you are already on Power BI you can access the Power Query editor by hitting the Transform Data button.
- Now in the Power Query Editor, select the column that contains DateTime values.
- Right-click on the selected column header and select Change Type , then select Date. This removes the time component, keeping only the date.
- Select whether to Create a new Transformation step (recommended) or overwrite the existing step.
- Hit Close and Apply.