Keep It Simple: Episode 01
Welcome to the first episode of our series where I'll be sharing tips and tricks on using Office 365 and Power BI. My aim is to help you work more efficiently, save time, and simplify the complexities associated with Power BI and Excel.
While I'm not certain about the duration of this series, I feel compelled to dive deeper into Power BI, particularly Power Query for this first edition.
Recently, I came across a user in a Power BI group who faced challenges with the date format. The format in question was Day/Month/Year, which is standard for the UK. Interestingly, this is also how French Canadians write the date in text. I believe this is where confusion arises. In Canada, the standard format is ISO 8601, meaning if your computer is set to French or English – Canada, Power BI will default to uploading a date in the Year/Month/Day format. Problems arise when trying to import a date in the Day/Month/Year format, as Power Query struggles to upload it correctly.
To add to the complexity, in the US, the date format is Month/Day/Year. That's three countries with three different date formats! It's no wonder that uploading dates in Power BI can be a challenge.
The user's query was straightforward: How can one upload the date without encountering issues?
While the solution is simple, I first want to highlight the user's approach. They employed a text function to extract the Year, Month, and Day, and then combined these three columns to match the format accepted by Power Query.
Though this method is ingenious, there's an even simpler solution!
Typically, to convert a column of data into a date format in Power Query, you'd click on the top left corner of the column and select 'Date'. However, this works seamlessly only if the date in your data matches the format of your local machine. If not, you might resort to creative methods, or better yet, use this straightforward trick:
Click on the top left corner of the column, but this time, select the 'Using Locale…' option at the bottom.
 
This action will prompt a screen where you can choose the date format of your source data. In our example, we're dealing with the UK format (Day-Month-Year).
 
Once you've made your selection, Power Query will convert your column into a date format, outputting it in the format of your local machine. In my case, it aligns with ISO 8601.
 
Â
And there you have it! With this nifty trick, you can easily manage most date formats from your source system in mere seconds!
Did you find this trick helpful? Share it and help a friend out! For more handy tips and insights on business or finance, sign up for my weekly newsletter. I'd love to have you on board!
Â
