How to manipulate Dates in Power BI

Power BI is powerful tool by Microsoft to analyze your data and create beautiful reports to understand it better. But recently, I came across a scenario which baffled me.

I had a SharePoint list containing some data about Products and their purchase dates (sample data of course :P) as shown below:

SharePoint List containing data about some Products and their Purchase Date

Now these dates were stored in a string format in SharePoint and was in US date notation. I had a scenario where I was getting this data as a string from the source. On changing it to date format with transform data, I got errors which wasn’t a surprise for me as my local date format is “dd-mm-yyyy”

Errors on changing to Date Format

But what was surprising was that there was no option to change the date format to US date notation i.e. “mm-dd-yyyy”.

Available date formats according to local system settings

To change your date to a format other than your local date format:

• Load the data from your source. It was SharePoint in my case.
• Transform the data as required. I had my date stored as string so transformed it to Date which gave error as my date in SharePoint was in mm-dd-yyyy format.

Transforming the Purchase Date from String to Date

Date data type giving error

• Now to avoid this error remove the Changed Type step and instead change the data type using locale. Choose the data type and locale (this is the format your original data is in like my date was in US format).

Removing the step

Changing the data type using Locale

Changing the type with Locale

• Boom the error goes away and date becomes in dd-mm-yyyy format. Save this transformation with Close and Apply.

• But in my report the requirement is mm-dd-yyyy format. Now go to model on left hand pane.

Purchase date changed from string to date format (dd-mm-yyyy)

• In formatting, choose custom format. And type the desired format which for me was mm-dd-yyyy. To show the difference I have changed it to mmmm-dd-yyyy.

Choosing custom option in formatting

Typing the custom date format

Date Format changed to “mmmm-dd-yyyy”

So this is how I manipulated dates. I hope this helps you out 😊