Date data wont change to date format

[michgork]

hello,

I have a set of external data where the DATE data seems to be impossible for PowerQ to format to date.

source data
[FONT=&quot]Date (DDMMYYYY)[/FONT]
[FONT=&quot]1062016[/FONT]
[FONT=&quot]1062016[/FONT]
[FONT=&quot]1062016[/FONT]
[FONT=&quot]1062016[/FONT]
[FONT=&quot]2062016[/FONT]
[FONT=&quot]2062016[/FONT]
[FONT=&quot]2062016[/FONT]
[FONT=&quot]3062016[/FONT]
[FONT=&quot]3062016[/FONT]
[FONT=&quot]3062016[/FONT]
[FONT=&quot]3062016[/FONT]
[FONT=&quot]3062016[/FONT]
[FONT=&quot]6062016[/FONT]
[FONT=&quot]6062016[/FONT]
[FONT=&quot]6062016[/FONT]
[FONT=&quot]6062016[/FONT]
[FONT=&quot]6062016[/FONT]
[FONT=&quot]6062016[/FONT]
[FONT=&quot]7062016[/FONT]
[FONT=&quot]7062016[/FONT]
[FONT=&quot]7062016[/FONT]
[FONT=&quot]7062016[/FONT]
[FONT=&quot]7062016[/FONT]
[FONT=&quot]7062016[/FONT]
[FONT=&quot]7062016[/FONT]
[FONT=&quot]7062016[/FONT]
[FONT=&quot]8062016[/FONT]
[FONT=&quot]8062016[/FONT]
[FONT=&quot]8062016[/FONT]
[FONT=&quot]8062016[/FONT]
[FONT=&quot]9062016[/FONT]
[FONT=&quot]9062016[/FONT]
[FONT=&quot]9062016[/FONT]
[FONT=&quot]9062016[/FONT]
[FONT=&quot]13062016

change type via 'using locale' english (uk), english (us) or danish (my own) results in:

[/FONT][FONT=&quot]Date (source) Date (changed)[/FONT]
[FONT=&quot]1062016 12-09-4807[/FONT]
[FONT=&quot]1062016 12-09-4807[/FONT]
[FONT=&quot]1062016 12-09-4807[/FONT]
[FONT=&quot]1062016 12-09-4807[/FONT]
[FONT=&quot]2062016 09-08-7545[/FONT]
[FONT=&quot]2062016 09-08-7545[/FONT]
[FONT=&quot]2062016 09-08-7545
the rest of the date changes results in error

I then tried to add 2 custom column
[/FONT]=Text.End("0"&Text.From([Column1]),6)
=Text.PadStart(Text.From([Month]),2,”0?)

so i have MMYYYY in one column and DD in another (i thought maybe the missing lead zero in the Day was the issue) and merged them into a new column which now have DDMMYYYY instead of DMMYYYY.

now im getting this error nomatter which way i try to format data type to date:
[FONT=&quot]DataFormat.Error: We couldn't parse the input provided as a Date value.[/FONT]
[FONT=&quot]Details:[/FONT]
[FONT=&quot]01062016

any ideas?

im a total newb with PowerQ btw

cheers
Michael[/FONT]

 

[SteelReyn]

Alright, I'm guessing if I play a little longer there's probably a more efficient way to do this. You could certainly break it out in multiple columns, but here it is in one.

1. Change the Date column to Text
2. Add a custom column :=Date.From(#date(Number.From(Text.End(Text.PadStart([Date], 8, "0"), 4)), Number.From(Text.Range(Text.PadStart([Date], 8, "0"), 2, 2)), Number.From(Text.Start(Text.PadStart([Date], 8, "0"), 2))))
3. Change the new column to a date

Edit: This one's a bit shorter :=Date.FromText(Text.End([Date], 4) & Text.Range(Text.PadStart([Date], 8, "0"), 2, 2) & Text.Start(Text.PadStart([Date], 8, "0"), 2))

 

[Bill Szysz]

Another way

let
    Source = Table.FromColumns({{1062016,3062016,8112016,15122016,31032016,1032016}}, {"Date"} ),
    ChType = Table.TransformColumnTypes(Source,{{"Date", type text}}),
    Transformation = Table.TransformColumns(ChType, {{"Date", each Date.From(Text.Combine(Splitter.SplitTextByRanges({{6,2},{4,2},{0,4}},true) (_), "-")), type date  }} )
in
    Transformation

Regards

 

[DanT]

Right off the bat it looks as if there are not enough numbers in your source data for the specified format -- DD MM YYYY would make the first entry 10 62 016...

 

[Ken Puls]

A little clunky, but since the issue seems to be that the Day varies between 1 and 2 characters, I split in from the right to carve off year and month, then re-joined them with the #date() function.

M code is here, and a sample workbook is attached.

let    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Dates", Int64.Type}}),
    #"Inserted Last Characters" = Table.AddColumn(#"Changed Type", "Last Characters", each Text.End(Text.From([Dates], "en-US"), 4), type text),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Last Characters",{"Last Characters"}),
    #"Split Column by Position" = Table.SplitColumn(Table.TransformColumnTypes(#"Removed Columns", {{"Dates", type text}}, "en-US"),"Dates",Splitter.SplitTextByPositions({0, 4}, true),{"Dates.1", "Dates.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Dates.1", Int64.Type}, {"Dates.2", Int64.Type}}),
    #"Split Column by Position1" = Table.SplitColumn(Table.TransformColumnTypes(#"Changed Type1", {{"Dates.1", type text}}, "en-US"),"Dates.1",Splitter.SplitTextByPositions({0, 2}, true),{"Dates.1.1", "Dates.1.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Position1",{{"Dates.1.1", Int64.Type}, {"Dates.1.2", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"Dates.2", "Year"}, {"Dates.1.2", "Month"}, {"Dates.1.1", "Day"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Date", each #date([Year],[Month],[Day])),
    #"Changed Type3" = Table.TransformColumnTypes(#"Added Custom",{{"Date", type date}})
in
    #"Changed Type3"

 

[michgork]

Thanks alot everybody!
I will try all of this out, if not just to get some more experience, and see if i can get it to work.
It's not one of those crucial things, but it's some data i'm gonna be getting on a regular basis once or twice a month from a subcontractor. I could just show the data as text but it's really annoying me and i just wanna crack it