Forum Discussion
Format
Hi guys, I have the following question. It is probably very trivial but I want to know how to make a date from "1022020" in pbi = "01.02.2020". Thank you very much! Abba
Alright, you should be able to follow the following steps:
Starting table with two dates:
Add this custom column to make all dates 8 digit:Column = if Text.Length([Date]) = 8 then [Date] else "0" & [Date]
Giving the following result:
Finally we add the last column to convert it to the correct format:Column 2 = Text.Start([Column] , 2) & "." & Text.Range([Column] , 2 , 2) & "." & Text.End([Column] , 4)
Giving the following result:
Which you can simply convert to date:
Br,
J
4 Replies
Do you want the date to remain a string/text value or do you also want it converted to datetype?
In Power Query you can use the following column:Column = Text.Start([Column] , 2) & "." & Text.Range([Column] , 2 , 2) & "." & Text.End([Column] , 4)
In Dax you can use the following:Column = LEFT('Table'[Column] , 2) & "." & RIGHT(LEFT('Table'[Column] , 4) , 2) & "." & RIGHT('Table'[Column] , 4)
Let me know how it goes.
/ JAlright, you should be able to follow the following steps:
Starting table with two dates:
Add this custom column to make all dates 8 digit:Column = if Text.Length([Date]) = 8 then [Date] else "0" & [Date]
Giving the following result:
Finally we add the last column to convert it to the correct format:Column 2 = Text.Start([Column] , 2) & "." & Text.Range([Column] , 2 , 2) & "." & Text.End([Column] , 4)
Giving the following result:
Which you can simply convert to date:
Br,
J
Hi Anonymous ,
If what you want is to display the date as ‘01.02.2020’, unfortunately, the current version of Power BI(October 2020) does not support this format.
Best regards,
Lionel ChenIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
