Transform date DD.MM.YYYY to DD/MM/YYYY

[Chanleakna123]

hi , I wanna transform my DD.MM.YYYY to DD/MM/YYYY , that it would be easier for me to link relationship with other table. 

but i failed to get it , even i click Change Type to "Date " , or i use MCode to transform it , it turns error in some date. 

how to success it ? 

[PattemManohar · Accepted solution]

@Chanleakna123 You can simply change the column type to "Date"

But anyway here is the custom column code you need to use..

Date.FromText([DateVal] as text) as date

[Chanleakna123]

hi @PattemManohar still in the same issue 😞 i tried using your M code , 

while it has a lots of row , i trina clear every format.  

how to settle this ? Do you have any idea ? 

[PattemManohar]

@Chanleakna123 Did you tried change the "Prod Date" field to Date data type.

Also, please post the same data in copiable format which will help us to replicate your issue.

[Chanleakna123]

hi @PattemManohar

https://drive.google.com/file/d/10GR5mn8hpMgmmQzGSshWbjLkS83ucNzy/view?usp=sharing

you can find the link above and try it 

[PattemManohar]

@Chanleakna123 Imported all Date values from the file provided (There are few blank rows, which are ignored)

So total 326 rows, the same formula worked nothing changed... Working fine for me...

[Chanleakna123]

Hi @Patt, really appreciated your quick response . Let me work from side and let u know the result. Big thanks

[Chanleakna123]

@PattemManohar not sure why it doesn't work on my table , i change the file to excel , do a lots of thing. 

OMG , 😞

[PattemManohar]

@Chanleakna123 Send me the "M Code" (From "Advanced Editor") you have on the table. 

[Chanleakna123]

let
Source = Excel.Workbook(File.Contents("C:\Users\hchanleakna\Desktop\Power BI\22-Syrup Yield\Monthly Syrup Yield Reporting.xlsx"), null, true),
Sheet2_Sheet = Source{[Item="Sheet2",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Sheet2_Sheet,{{"Column1", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each Date.FromText([Date] as text) as date)
in
#"Added Custom"

HI @PattemManohar

[PattemManohar]

@Chanleakna123 Could you please confirm that you are using the same excel file that was shared in google drive ? As I can see only one sheet in the excel that was shared.

[Chanleakna123]

@Patt , this one no use . As I remove in BI . And I will use only Prod Date onward.

[PattemManohar]

@Chanleakna123 But this is the excel file you have provided and I have used Prod Date column from this file only.

[Chanleakna123]

@PattemManohar hi , i changed another source of the file , and it works , thanks you so much.