Forum Discussion
Date format and table layouts
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.
Most importantly, that unpviot suggestion worked perfectly, thanks so much. I first tried it on a small set of data for a single year - to make sure I understand how it works:
In my example there is one year of data, so 12 YYYYMM columns. Each row contains an expense for a particular location, most locations have multiple rows with different expense types. By unpivoting this data, it's reducing to a single column that has the YYYYMM date values, and adding a row for each of the different months? This was extremely helpful, thanks!
For some reason, however, I'm getting erros when trying to add a custom column. I entered the formula you posted, and got an error stating "Expression.Error: The name 'DATE' wasn't recognized. Make sure it's spelled correctly.". I played around some more and got the same error using the =LEFT(...., =MID(......, etc. - do you know why that might be the case?
Are you adding this custom column in through the "Query Editor" or through the Data screen as part of Power BI? The formula's i've shown there are DAX formulas, not Power Query formulas. There should be a Power Query equivelent if you wish to do it there.
Sorry for the late reply, I was sent out of the country for a while. To answer your question, I was adding the column through the Query Editor. I'm not familiar with adding the column through the Data screen. I guess I assumed DAX could be used through the Query Editor, but apparently not?
Anonymous wrote:= DATE(LEFT([OLDDATE], 4), RIGHT([OLDDATE], 2), 1)
Is this supposed to work in PowerQuery (Excel) too?
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.
