Hi Mynda
I'm working through 6.13 in your PowerQuery course. I thought I'd try using "if" to create a financial year column from the sample data you provide (Lynx, Venus, Aries, Mars etc project list). I'm doing something wrong and getting an error.
Basing the calculation on the Start Date column, I want to create a column indicating the Financial year (Jul-Jun) the project started in. Here's the formula I'm using that gives me an error:
FinYearStart = if Date.Month[StartDate]<=6 then Date.Year([StartDate])-1 & Date.Year([StartDate]) else Date.Year([StartDate]) & Date.Year([StartDate])+1
I want it to concatenate the values from the two dates so for a date like 4/08/2015 the result should be 20152016 [as text] and for 6/02/2017 the result should be 20162017. I can see I'm missing a conversion to text, how do I do that? And is that my only cause of error? Is there a better way to do this?
The course is outstanding btw, I am learning a ton.
Thanks,
Catherine
Hi Catherine,
It's great to hear you're enjoying the course and even better to see you're practicing and trying out your own ideas. You're on the right track with needing to convert the numbers to text. You can use Number.ToText for this like so:
=if Date.Month([StartDate])<=6 then Number.ToText(Date.Year([StartDate])-1) & Number.ToText(Date.Year([StartDate])) else Number.ToText(Date.Year([StartDate])) & Number.ToText(Date.Year([StartDate])+1)) Mynda
Thanks Mynda, that's what I needed.
I also figured out how to make it two digits (in case anyone else is interested):
=if Date.Month([StartDate])<=6
then Text.End(Number.ToText(Date.Year([StartDate])-1),2) & Text.End(Number.ToText(Date.Year([StartDate])),2)
else Text.End(Number.ToText(Date.Year([StartDate])),2) & Text.End(Number.ToText(Date.Year([StartDate])+1),2)
