Date format and table layouts

[mterry]

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 other tables, the date is actually the header of a column (typically in the same format). I'm extremely new to PBI, and I'm wondering if it's going to be possible to get these to all play nice given the different layouts? From playing around I'm having trouble working with the visuals for the tables that have the dates set as column headers.

I'm also having a smaller issue with the YYYYMM format in general, and not sure if whether it's my source data, me, or PBI. I am able to work around the issue using a find and replace since there are only so many months in a year and my data only spans a few years, but I'm wondering if it's possible to get PBI to recognize that format and utilize it?

Thanks in advance for any assistance....

[Anonymous]

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]

---

@Anonymous wrote:

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

---

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

[Anonymous]

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.

[mterry]

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?

[Anonymous]

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.

[mterry]

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]

DAX is the language used in the main Power BI desktop screens.  A completely different language is used called Power Query (also referred to as M).

[mterry]

I didn't realize they used different languages. I'll attempt your suggestion in the power bi screen. If I make the change there, will it update the data table in the query screen as well?

[Anonymous]

No it wont.  Think of the "Edit Query" screen as a place to prepare the data prior to it being loaded DAX and having the "Manage Relationships" kick in.  There are often times where doing things in DAX or Power Query will give you the same result, so which ever one you choose is up to you.  If you need the changes to be inside your Edit Queries area, you would need to take the same Idea in that Dax expression and find the power Query equivilent.

I'm sure many of us can help with that.  I'll try, i just didn't have time prior to clicking 'Post' on this response.

[Anonymous]

Ok try this.  Go into Query Editor and select Custom Column.  You should get a pop up window that lets you name the column and has a box with "=" in it.  Place in

Date.FromText(Text.Range([OLDDATE], 0, 4) & "-" & Text.Range([OLDDATE], 4, 2) & "-01")

Then all you need to do is highlight [OldDate] and click on the correct fields from the field picker.  Just make sure you don't remove a comma by accident.