Date Format

[Anonymous]

Hello there,

short question. I have a date column in a CSV File with that format 01032018 ddmmyyyy

When I change the format from number to date, I just get 30.12.1899 dd.mm.yyyy.

Is there a way to change the Format in M Query or shall I add another column, where I split the number in three parts and put points between them?

#"Geänderter Typ1" = Table.TransformColumnTypes(#"Geänderter Typ",{{"Genehmigungsdat", type date}})

Looking forward for any hints :).

[Greg_Deckler · Accepted solution]

New column:

=Text.Combine({Text.Middle(Text.From([Column1], "en-US"), 2, 1), "/", Text.Start(Text.From([Column1], "en-US"), 1), "/", Text.Middle(Text.From([Column1], "en-US"), 3)})

[Greg_Deckler · Accepted solution]

New column:

=Text.Combine({Text.Middle(Text.From([Column1], "en-US"), 2, 1), "/", Text.Start(Text.From([Column1], "en-US"), 1), "/", Text.Middle(Text.From([Column1], "en-US"), 3)})

[Anonymous]

Thanks for your suggestion Greg,

I tried to use your example like that:

#"Genehmigungdat" = Text.Combine({Text.Middle(Text.From([Genehmigungsdat], "en-US"), 2, 1), "/", Text.Start(Text.From([Genehmigungsdat], "en-US"), 1), "/", Text.Middle(Text.From([Genehmigungsdat], "en-US"), 3)})

but that didn't work out. Maybe I made the wrong placement. I am not that used to M.

By the way, i noticed that there are different lengths like 10102015 or 1102015 (null is missing).

Can your suggestion handle that aswell?

[v-yulgu-msft]

Hi @Anonymous,

but that didn't work out. Maybe I made the wrong placement. I am not that used to M.

You should change the last step name once you have added new step in Power code.

---

By the way, i noticed that there are different lengths like 10102015 or 1102015 (null is missing). 

---

Could you provide some examples of date with different length? I have one concern, if the length of dates is not fixed, take 1112015 as an example, should we regard it as 1 November 2015 or 11 January 2015?

Regards,

Yuliana Gu

[Anonymous · Accepted solution]

Afer some time I found a very proper Solution. I just changed the type from text to date in the Advanced Query Editor. And now the date is refered as date without any new column in table or caluclation in Editor.

Sorry for let you waiting so long.

And thanks v-xulgu-msft and Greg_Deckler...just missed that little mistake I made. Thanks a lot.