text CYYMMDD to Date MMDDYYYY

[sricks]

I'm working with AS400 information that is bring dates into my query formatted as text CYYMMDD. I need this to be converted to a date. I cleaned up the data to make sure there was no other trailing spaces, and tried Date.Fromtext, but I'm still getting an error. Most of the help I am stumbling across is relating to excel formulas that aren't easily "translated" to a Power Query, and I believe that I am missing a step but have no clue which step I am missing. Any help is appreciated!

 

[shg]

I don't use Power BI -- will a formula do?

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][/tr][tr][td]

1​

[/td][td]0911231[/td][td]

12/31/1991​

[/td][td]B1: =--TEXT(19 + LEFT(A1) & MID(A1, 2, 6), "0000-00-00")[/td][/tr]
[tr][td]

2​

[/td][td]1150602[/td][td]

06/02/2015​

[/td][td][/td][/tr]
[/table]

 

[sricks]

shg,

No the formula wont work due syntax, but thanks!

 

[ImkeF]

Looks like you're using the right formula. Hard to tell whats wrong without knowing what the error message says.

But maybe you try recreating the source for that formula using the Text.Range formula for example (https://support.office.com/en-sg/ar...f56-8188-3e8f435ae607?ui=en-US&rs=en-SG&ad=SG). This could bring some irregularities to the surface.

 

[billszysz]

Hi sricks

There are many ways to do this but this one is using User Interface only (M language knowledge is not necessary).

let
    Source = Excel.CurrentWorkbook(){[Name="Tabela1"]}[Content],
    SplitCol1 = Table.SplitColumn(Source,"dates",Splitter.SplitTextByPositions({0, 1}, false),{"dates.1", "dates.2"}),
    RemCol = Table.RemoveColumns(SplitCol1,{"dates.1"}),
    SplitCol2 = Table.SplitColumn(RemCol,"dates.2",Splitter.SplitTextByRepeatedLengths(2),{"dates.2.1", "dates.2.2", "dates.2.3"}),
    CombineCols = Table.CombineColumns(SplitCol2,{"dates.2.2", "dates.2.3", "dates.2.1"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"Text dates"),
    ChType = Table.TransformColumnTypes(CombineCols, {{"Text dates", type date}}, "en-US")
in
    ChType

Of course you can use your own data source instead of Excel.CurrentWorkbook.
Imkef's advice is also good way

Regards