Manage Date easily in Power BI with Power Query

Manage Date easily in Power BI with Power Query

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.

 Power Query - Data Type

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).

 Power Query - Data Type Locale

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.

 Power Query - Date 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!

 

Back to blog

Leave a comment

Please note, comments need to be approved before they are published.

  • The Benefit of Automation for Accounting and Financial Aspects in Business

    The Benefit of Automation for Accounting and Fi...

      Introduction In the modern business environment, automation stands as a pillar of efficiency and accuracy. This is especially true in the realms of accounting and finance where precision is...

    The Benefit of Automation for Accounting and Fi...

      Introduction In the modern business environment, automation stands as a pillar of efficiency and accuracy. This is especially true in the realms of accounting and finance where precision is...

  • Why I Transitioned from Excel to Power BI

    Why I Transitioned from Excel to Power BI

    Why I Transitioned from Excel to Power BI and (Why I believe Team Leader Should Consider Making the Switch)As a tech enthusiast, I’m always eager to explore innovative tools that...

    Why I Transitioned from Excel to Power BI

    Why I Transitioned from Excel to Power BI and (Why I believe Team Leader Should Consider Making the Switch)As a tech enthusiast, I’m always eager to explore innovative tools that...

  • Navigating Data's Potential: Crafting the Ideal Power BI Project for Your Enterprise

    Navigating Data's Potential: Crafting the Ideal...

    In the digital age, data's value is unparalleled, acting as the linchpin of strategic insights and decision-making. Microsoft Power BI emerges as a crucial ally for organizations keen on deciphering...

    Navigating Data's Potential: Crafting the Ideal...

    In the digital age, data's value is unparalleled, acting as the linchpin of strategic insights and decision-making. Microsoft Power BI emerges as a crucial ally for organizations keen on deciphering...

1 of 3