Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Date format and table layouts

I have a few different tables I'm hoping to link up, but the layout is slightly different. On one of the tables, there is a column that includes the date of the transactions in YYYYMM format. In the ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
9 years ago

Just to clarify.  When you say the YYYYMM is "actually the header of a column", do you mean that you have a table with multiple columns and each column will be its own YYYYMM value?

If thats the case, get the Query Editor to 'Unpivot' those columns such that it places them back into separate rows with only a single column.  That should allow you to link those tables.

Using the YYYYMM format:  An easy solution can be to create a formula column where you convert YYYYMM into dates making use of the LEFT and RIGHT along with the DATE function.  This might look something like

= DATE(LEFT([OLDDATE], 4), RIGHT([OLDDATE], 2), 1)
which would change 201704 into a date of 1st April 2017.

benjamin_sasin's avatar
benjamin_sasin
Icon for Resolver I rankResolver I
7 years ago
Anonymous wrote:

= DATE(LEFT([OLDDATE], 4), RIGHT([OLDDATE], 2), 1)

Is this supposed to work in PowerQuery (Excel) too? 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    You can do something similar in Power Query.  LEFT would be Text.Start.  Right would be Text.End.  Date isn't supported, however you can use Date.FromText to do something similar.  I get the best results when i use Date.FromText and put the date data in a YYYY-MM-DD format first.