M Type source load from datalake converting date to US format when it can and rest to UK format

[Anonymous]

Power BI data source from datalake 

eg expression below

The data source date are in the uk format dd/MM/yyyy
When I process the cube for some reason it will convert the dates to US format if it can and leave the rest to UK format.
So we end up with mixed date formats in the cube. Which is incorrect.
Image below to demostrate.
2nd and 5th row it was swapped to MM/dd/yyyy while the 3rd row is dd/MM/yyyy. 
Column one is the original date in text so we can compare while column two is the processed date. 

But when we get data from sql server this issue doesnt occur. Image the correct behaviour getting from sql server. 

So concluding this is the issue with the DeltaLake.Table(TableSource) function in m type. Anyone else having this issue ? 

[Anonymous · Accepted solution]

Hi @Anonymous ,
According to your description, there is mixed formatting when using M code to get the table from DataLake. The first thing we need to make clear is that the final data retrieved in the power query should be in the same format for each column, so your “mixed columns” are all recognized as US format, if you want them all to be in UK format, you can do a format conversion in m code

Source:{
"Type": "m",
"expression": [
	"let",
	"	Source = AzureStorage.DataLake(...)",
	"	TTableSource" = Source{...}",
	"	ToDelta" = DaltaLake.Table(),
	"	ConvertedDates = Table.TransformColumns(ToDelta, {{"DateColumn", each Date.FromText(_, "en-GB"), type date}})",
	"in",
	"	ConvertedDates"
	]
}

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly