I work with Power BI almost every day, nevertheless there are sometimes moments where the tool surprises me. One of this moments I had two weeks ago, when a colleague showed me an easy way to convert date from the US Format (mm.dd.yyyy) to the german format (dd.mm.yyyy). This way was so much easier, then my previous approach, so I like to share this with you. In the first part of this blog post I will show you my old approach by using an M function. After that I show you the easy way by using the query editor of Power BI.
Nearly every Power BI model I created in the last 2 years, had an date table for time intelligence calculation or just for convenience. The problem here is depending on the language setting the date is interpreted differently. In the US a date often is formatted like this mm.dd.yyyy in Germany it is dd.mm.yyyy. Often if you try to change the data typ from text (imported from csv for example) to date in power bi, it fails. This happens because Power bi can’t recognize the date pattern. The standard date pattern is based on your windows region settings.
This is not a realy tough probelm. You can easily split the date column rearrange it an build a new formatted date text with the query editor. But I have done this so often, that it starts to annoy me. So I wrote a custom M function.
The function has 3 Inputs:
1. A datecolumn, the column can contain a date, a number or a text value.
2. The current date scheme needs to be a generic representation of the date by using y for year, m for month and d for day. With this three symbols you can build a representation of your current date structure for example mm.dd.yyyy
3. The targetScheme column represents your export date structure for example dd.mm.yyyy or yyyymmdd
The function will fill the placeholder y,m and d in the form with the character in the orignal date column. I think the M code is quite easy to read, in total it is simple a string replacement.
-
let
-
Converter = (dateColumn as any,currentScheme as text, targetScheme as text)=>
-
let
-
content = try Date.ToText(dateColumn) otherwise try Number.ToText(dateColumn) otherwise dateColumn,
-
YearPosition = Text.PositionOfAny (currentScheme, {„Y“,„y“},Occurrence.All),
-
YearTarget = Text.PositionOfAny (targetScheme, {„Y“,„y“}, Occurrence.All),
-
LengthOfYearTarget = List.Count(YearTarget),
-
StartYear = List.First(YearPosition),
-
YearResult = Text.Range( content ,StartYear,LengthOfYearTarget),
-
MonthPosition = Text.PositionOfAny (currentScheme, {„M“,„m“},Occurrence.All),
-
MonthTarget = Text.PositionOfAny (targetScheme, {„M“,„m“}, Occurrence.All),
-
LengthOfMonthTarget = List.Count(MonthTarget),
-
StartMonth = List.First(MonthPosition),
-
MonthResult = Text.Range(content,StartMonth,LengthOfMonthTarget),
-
DayPosition = Text.PositionOfAny (currentScheme, {„D“,„d“},Occurrence.All),
-
DayTarget = Text.PositionOfAny (targetScheme, {„D“,„d“}, Occurrence.All),
-
LengthOfDayTarget = List.Count(DayTarget),
-
StartDay = List.First(DayPosition),
-
DayResult = Text.Range(content,StartDay,LengthOfDayTarget),
-
FullDateYear = Text.ReplaceRange(targetScheme,List.First(YearTarget),Text.Length(YearResult),YearResult),
-
FullDateMonth = Text.ReplaceRange(FullDateYear,List.First(MonthTarget),Text.Length(MonthResult),MonthResult),
-
Result = Text.ReplaceRange(FullDateMonth,List.First(DayTarget),Text.Length(DayResult),DayResult)
-
in Result
-
in Converter
You can use this function by clicking on „New Source“ and „Blank Query“ and inserting the M-Code into the Advanced Editor.
You can use this function to create a new column by clicking on the „Invoke Custom Function“ button. Here you can select the function, choose your bad formated date column, enter your schemes. Thats all.
The cool thing about this you can use the separator you like. Even better you don’t have to use a separator at all. So you can use this function for example for transforming a date key column from a data warehouse (yyyymmdd) to a normal date (dd.mm.yyyy). This is very use full, because the Power BI data model has some limitations concerning time intelligence function in DAX. You have to use columns of the typ date if you want to use function like Datesytd or Sameperiodlastyear in DAX. So if you use a date key column you can not use the time intelligence!
Here comes the easy way Power BI magic
In the query editor you can use the „Using Locale“ menu point. Simply right click on the date column and select Change Type/Using Locale.
Now you can choose the format (int, string, date , etc.) and the the Locale and that’s it.
Awesome!
Conclusion
What did I learn from this story? It is a good idea to leave known paths once in a while, and try something new. For the so fast changing Power BI this hint is even more important.
Kommentare (6)