Powerquery transform text to date conversion error

S

smd747

New member
Joined
Nov 22, 2016
Messages
13
Reaction score
0
Points
0
I have on a daily basis text dates ranging from four digits to eight digits. I have developed VBA code to clean up for me. I have been unable to come up with a way to clean this up in power query using transform. It's frustrating because I can do all the other cleanup but unable to clean up this one field. I have an M for data monkey and have searched the web and have found nothing I could use as a solution

Here is what the dates look like all different lengths

Date
91616
10232016
111315
7116


here is my VBA code that does the job.

Code: 
Sub ChangeTextToDate()
  Dim r             As Range
  Dim cell          As Range
  Dim d             As Double
  On Error Resume Next
  Set r = ActiveWindow.RangeSelection.SpecialCells(xlCellTypeConstants, xlNumbers).Cells
  If Err.Number Then GoTo NothingToDo
  For Each cell In r.Cells
    If VarType(cell.Value) <> vbDate Then
      d = Int(cell.Value2)
      Select Case d
        Case 1000 To 9999
          cell.Value = CDate(Format(d, "0-0-00"))
        Case 10000 To 999999
          cell.Value = CDate(Format(d, "0-00-00"))
        Case Is > 100000
          cell.Value = CDate(Format(d, "0-00-0000"))
      End Select
    End If
  Next cell
NothingToDo:
End Sub


Is this possible in powerquery ?
looking to do all transform and prep using powerquery. Any help and direction would be greatly appreciated.
 
That works, amazing just like the VBA solution. How do I implement it, use it to trigger transformation. Amazing thanks
 
The date column to convert is "J" Order Date I am still struggling with how to invoke the function
 
let
fxToDate = (num as number) =>
let
ToText = Text.From(num),
Lengths = Text.Length(ToText),
Lists = {{Lengths = 4, {2,1,1}},
{Lengths = 5, {2,2,1}},
{Lengths = 6, {2,2,2}},
{Lengths = 8, {4,2,2}}
},
ListForSplitter = List.First(List.Select(Lists, each _{0}=true)){1},
Split = Splitter.SplitTextByLengths(ListForSplitter, true) (ToText),
TextCombine = Text.Combine(Split, "/"),
TransToDate = Date.From(TextCombine, "en-US")

in
TransToDate,



Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type number}}),
Transform = Table.TransformColumns(#"Changed Type", {{"Date", each fxToDate(_) }} ),
#"Changed Type1" = Table.TransformColumnTypes(Transform,{{"Date", type date}})
in
#"Changed Type1"




Here is my sheet. The date column to convert is "J" Order Date


let
Source = Folder.Files("C:\Users\Stephen\OneDrive\Aging PowerQeryTest"),
#"Filtered Rows" = Table.SelectRows(Source, each ([Name] = "OPEN ORDERS.csv" or [Name] = "RESERVE ORDERS.csv")),
#"Combined Binaries" = Binary.Combine(#"Filtered Rows"[Content]),
#"Imported CSV" = Csv.Document(#"Combined Binaries",[Delimiter=",", Columns=16, Encoding=1250, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(#"Imported CSV"),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ORDER STATUS CODE", type text}, {"BRANCH NUMBER", Int64.Type}, {"SHIP FROM BRANCH NUMBER", Int64.Type}, {"ENTERED BY INITIALS", type text}, {"SALESMAN ID", type text}, {"CUSTOMER NUMBER", Int64.Type}, {"CUSTOMER NAME", type text}, {"ORDER NUMBER", type text}, {"ORDER CHRG OR CSH CODE", type text}, {"ORDER DATE", Int64.Type}, {"PURCH.SECTION CODE", Int64.Type}, {"OUR ITEM NUMBER", Int64.Type}, {"OUR PRODUCT NUM.", type text}, {"QUANTITY SHIPPED", Int64.Type}, {"EXTENDED COST", type text}, {"", type text}}),
#"Filtered Rows1" = Table.SelectRows(#"Changed Type", each ([ORDER STATUS CODE] <> "ORDER STATUS CODE"))
in
#"Filtered Rows1"
 
I tried for hours with no luck adapting. This was close but NG I get errors

let
fxToDate = (num as number) =>
let
ToText = Text.From(num),
Lengths = Text.Length(ToText),
Lists = {{Lengths = 4, {2,1,1}},
{Lengths = 5, {2,2,1}},
{Lengths = 6, {2,2,2}},
{Lengths = 8, {4,2,2}}
},
ListForSplitter = List.First(List.Select(Lists, each _{0}=true)){1},
Split = Splitter.SplitTextByLengths(ListForSplitter, true) (ToText),
TextCombine = Text.Combine(Split, "/"),
TransToDate = Date.From(TextCombine, "en-US")

in
TransToDate,

Source = Folder.Files("C:\Users\Stephen\OneDrive\Aging PowerQeryTest"),
#"Filtered Rows" = Table.SelectRows(Source, each ([Name] = "OPEN ORDERS.csv" or [Name] = "RESERVE ORDERS.csv")),
#"Combined Binaries" = Binary.Combine(#"Filtered Rows"[Content]),
#"Imported CSV" = Csv.Document(#"Combined Binaries",[Delimiter=",", Columns=16, Encoding=1250, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(#"Imported CSV"),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ORDER STATUS CODE", type text}, {"BRANCH NUMBER", Int64.Type}, {"SHIP FROM BRANCH NUMBER", Int64.Type}, {"ENTERED BY INITIALS", type text}, {"SALESMAN ID", type text}, {"CUSTOMER NUMBER", Int64.Type}, {"CUSTOMER NAME", type text}, {"ORDER NUMBER", type text}, {"ORDER CHRG OR CSH CODE", type text}, {"ORDER DATE", Int64.Type}, {"PURCH.SECTION CODE", Int64.Type}, {"OUR ITEM NUMBER", Int64.Type}, {"OUR PRODUCT NUM.", type text}, {"QUANTITY SHIPPED", Int64.Type}, {"EXTENDED COST", type text}, {"", type text}}),
#"Filtered Rows1" = Table.SelectRows(#"Changed Type", each ([ORDER STATUS CODE] <> "ORDER STATUS CODE"))
#"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows1",{{"ORDER DATE", type number}})
#"Changed Type1" = Transform = Table.TransformColumns(#"Changed Type", {{"ORDER DATE", each fxToDate(_)}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows1",{{"ORDER DATE", type date}})

in
#"Changed Type1"
 
Sorry, I was out of range till now.
Try this...

let
fxToDate = (num as number) =>
let
ToText = Text.From(num),
Lengths = Text.Length(ToText),
Lists = {{Lengths = 4, {2,1,1}},
{Lengths = 5, {2,2,1}},
{Lengths = 6, {2,2,2}},
{Lengths = 8, {4,2,2}}
},
ListForSplitter = List.First(List.Select(Lists, each _{0}=true)){1},
Split = Splitter.SplitTextByLengths(ListForSplitter, true) (ToText),
TextCombine = Text.Combine(Split, "/"),
TransToDate = Date.From(TextCombine, "en-US")

in
TransToDate,

Source = Folder.Files("C:\Users\Stephen\OneDrive\Aging PowerQeryTest"),
#"Filtered Rows" = Table.SelectRows(Source, each ([Name] = "OPEN ORDERS.csv" or [Name] = "RESERVE ORDERS.csv")),
#"Combined Binaries" = Binary.Combine(#"Filtered Rows"[Content]),
#"Imported CSV" = Csv.Document(#"Combined Binaries",[Delimiter=",", Columns=16, Encoding=1250, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(#"Imported CSV"),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ORDER STATUS CODE", type text}, {"BRANCH NUMBER", Int64.Type}, {"SHIP FROM BRANCH NUMBER", Int64.Type}, {"ENTERED BY INITIALS", type text}, {"SALESMAN ID", type text}, {"CUSTOMER NUMBER", Int64.Type}, {"CUSTOMER NAME", type text}, {"ORDER NUMBER", type text}, {"ORDER CHRG OR CSH CODE", type text}, {"ORDER DATE", Int64.Type}, {"PURCH.SECTION CODE", Int64.Type}, {"OUR ITEM NUMBER", Int64.Type}, {"OUR PRODUCT NUM.", type text}, {"QUANTITY SHIPPED", Int64.Type}, {"EXTENDED COST", type text}, {"", type text}}),
#"Filtered Rows1" = Table.SelectRows(#"Changed Type", each ([ORDER STATUS CODE] <> "ORDER STATUS CODE")) , //Should be a comma at the end
// #"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows1",{{"ORDER DATE", type number}}), this is not necessary
Transform = Table.TransformColumns(#"Filtered Rows1", {{"ORDER DATE", each fxToDate(_)}}),
#"Changed Type1" = Table.TransformColumnTypes(Transform,{{"ORDER DATE", type date}})

in
#"Changed Type1"

Regards
 
Here is the final that works thanks for all your help and directions. Great education with the advance editor

let
fxToDate = (num as number) =>
let
ToText = Text.From(num),
Lengths = Text.Length(ToText),
Lists = {{Lengths = 4, {2,1,1}},
{Lengths = 5, {2,2,1}},
{Lengths = 6, {2,2,2}},
{Lengths = 8, {4,2,2}}
},
ListForSplitter = List.First(List.Select(Lists, each _{0}=true)){1},
Split = Splitter.SplitTextByLengths(ListForSplitter, true) (ToText),
TextCombine = Text.Combine(Split, "/"),
TransToDate = Date.From(TextCombine, "en-US")

in
TransToDate,


Source = Folder.Files("C:\Users\Stephen\OneDrive\Aging PowerQeryTest"),
#"Filtered Rows" = Table.SelectRows(Source, each ([Name] = "OPEN ORDERS.csv" or [Name] = "RESERVE ORDERS.csv")),
#"Combined Binaries" = Binary.Combine(#"Filtered Rows"[Content]),
#"Imported CSV" = Csv.Document(#"Combined Binaries",[Delimiter=",", Columns=16, Encoding=1250, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(#"Imported CSV"),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ORDER STATUS CODE", type text}, {"BRANCH NUMBER", Int64.Type}, {"SHIP FROM BRANCH NUMBER", Int64.Type}, {"ENTERED BY INITIALS", type text}, {"SALESMAN ID", type text}, {"CUSTOMER NUMBER", Int64.Type}, {"CUSTOMER NAME", type text}, {"ORDER NUMBER", type text}, {"ORDER CHRG OR CSH CODE", type text}, {"ORDER DATE", Int64.Type}, {"PURCH.SECTION CODE", Int64.Type}, {"OUR ITEM NUMBER", Int64.Type}, {"OUR PRODUCT NUM.", type text}, {"QUANTITY SHIPPED", Int64.Type}, {"EXTENDED COST", type text}, {"", type text}}),
#"Filtered Rows1" = Table.SelectRows(#"Changed Type", each ([ORDER STATUS CODE] <> "ORDER STATUS CODE")),

#"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows",{{"ORDER DATE", type number}}),
Transform = Table.TransformColumns(#"Changed Type", {{"ORDER DATE", each fxToDate(_) }} ),
#"Changed Type2" = Table.TransformColumnTypes(Transform,{{"ORDER DATE", type date}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type2",{""})
in
#"Removed Columns"

Thanks Bill much appreciated, thanks
 
You must log in or register to reply here.
Back
Top