Site Loader
Power BI

Power BI Logo

In this post we will look at taking a regular date column and turning it into a French Translated date. You should have worked through the previous posts as we will be using the pbix file that was created in GOC Power BI tips #3 – Automatic translation from an English to a French Month.

Getting started

In our previous posts we took the date and Department data and in the Power BI query screen we added Year, Month and Quarter. Using the same process you now need to add a “Day” column.

To recap (in the Power BI query screen) you do this by highlighting the “Date” column, then click on “Add Column”, then click on the “Date” button in the ribbon, then “Day” and again “Day” in the sub menu items. Once you have done this you should see the following:

Add day query

Once you are good with that hit “Home” then “Close and apply” to get into Power BI.

Adding in calculated columns to do the translation

So in the previous post we entered a translation cross reference table, if you have not done that exercise I suggest that you go back and complete that. Assuming that you are good, we now have to create a new Calculated column and add in the following DAX:

Calendar Date Fr = Data[Day]&" "&Data[Month Fr]&". "&Data[Year]

Once you have done that you should see the following:

French calculated column

NOTE that for this to work you need to have added the Calculated column “Month Fr” from the last post.

So what is going on here?

We are combining three separate columns into a single column using the “&” operator – namely the “Day”, “Month Fr” and “Year” columns – simple as that!

We still have the same sorting issue that we did in our last post, the fact that this is a text column and not a native date column means that we need to force a manual sort on our graphs. The other disadvantage with this approach is that Power BI will not build an automatic date hierarchy. I will show you how to do build a manual date hierarchy in a follow up post.

Final note: This is NOT as flexible as changing the language and building all of your charts in French, as this is a text field there are certain limitations (most of which can be solved by manually building a French date hierarchy) but you might not get all of the behaviours you want from all of your charts.

In my experience though the ability to build a set of charts in which you can dynamically switch between English and French outweighs the limitations. See below for a Power BI file I created, it has a simple table that shows the dates vs fiscal year (for count of Department). Have a play and see what you can come up with!

Coming next in GOC Power BI tips #5: Dynamically changing between English and French on your visualization.

Please leave questions in the comments or contact us if you want to know more. If you are interested in getting to know Power BI or to have some more advanced training check out our Data Engineering and Tools workshops.


Post Author: Stephen Davies

Stephen is the CEO of DAVHILL Group, a Business Intelligence and Data Analytics company based in Ottawa, Ontario.

He has more than 25 years of experience working in both public and private sectors. Early in his career in operations management he started to do data analysis and never quite escaped.

With an academic background in Physics and Engineering he has worked in semiconductor and OEM manufacturing as an engineer and supply chain director. As a Business Intelligence and Process Transformation consultant he seems to spend most of his time either playing with aircraft in the aviation industry or with the Federal Canadian Government.

As the CEO of the DAVHILL Group, Stephen's main focus is to make data real and useable for everybody. DAVHILL specializes in Business Intelligence, Artificial Intelligence and Machine Learning systems implementation and is delighted to provide training courses to the Data Action Lab.

Leave a Reply

Your email address will not be published. Required fields are marked *