How can I add a column to a merged table created with Power Query in Excel 2016?

Question

I have an Excel 2016 file with several tables that I have joined together using Power Query. The tables have some date fields that are populated as text (e.g. "1022018" = Jan 2, 2018) and I need to convert them to dates. I added a column for each that converts the data just fine but every time I refresh my query, the new columns disappear.

I opened Power Query and tried adding a custom column but the formula I created in Excel does not work and it needs to be written in M. I haven't had any luck finding an easy conversion to M or another way to create the columns using the Excel formula.

Any ideas how to accomplish this?

FYI here is formula: =IF(LEN([@DOBstring])=7,DATE(RIGHT([@DOBstring],4),LEFT([@DOBstring],1),MID([@DOBstring],2,2)),DATE(RIGHT([@DOBstring],4),LEFT([@DOBstring],2),MID([@DOBstring],3,2)))

chillin chillin · Accepted Answer · 2019-09-19T15:27:37

If I've understood correctly, the month in the input string isn't zero-padded (causing input strings to be of length either 7 or 8).

It might be worth creating a small function like the below, which will handle the varying length and then attempt to parse a date based on assumed character positions.

ParseDate = (someText as text) as date =>
        let
            zeroPadded = Text.End("0" & someText, 8),
            yearParsed = Text.End(zeroPadded, 4),
            monthParsed = Text.Start(zeroPadded, 2),
            dayParsed = Text.Middle(zeroPadded, 2, 2),
            dateParsed = #date(Number.From(yearParsed), Number.From(monthParsed), Number.From(dayParsed))
        in dateParsed,

You can then invoke it when transforming an existing column, or adding a new column. To give you an example:

let
    ParseDate = (someText as text) as date =>
        let
            zeroPadded = Text.End("0" & someText, 8),
            yearParsed = Text.End(zeroPadded, 4),
            monthParsed = Text.Start(zeroPadded, 2),
            dayParsed = Text.Middle(zeroPadded, 2, 2),
            dateParsed = #date(Number.From(yearParsed), Number.From(monthParsed), Number.From(dayParsed))
        in dateParsed,
    someTable = Table.FromColumns({{"1022018", "05242019", "12282026"}}, type table [toParse = text]),
    transformedColumn = Table.TransformColumns(someTable, {{"toParse", ParseDate, type date}}),
    addedColumn = Table.AddColumn(someTable, "parsed", each ParseDate([toParse]), type date)
in
    addedColumn

which gives:

How can I add a column to a merged table created with Power Query in Excel 2016?

2 Answers