Custom Column Query

[Adam13Hylo]

Hi all,

Wondering if someone can help me please. I have trying to write a query so that if a column is less than 8 then this will combine a string and another column. However for some reason it is continiously showing blank.

The formula I have is: 

if [ReportingMonthNum] < 8 then "FY" & [YearNum] else ""

The reporting monht column has values ranging from 1 to 13 and the "YearNum" will show our year number for example, 15, 16 or 17 etc... I am trying to combine this with some text "FY" however my columns are continuing to show blank.

Can anyone point me as to where I am going wrong please.

[MattAllington · Accepted solution]

You can't concatenation text with numbers. Convert the number first.

Number.ToText([YearNum])

[Adam13Hylo]

Thank you this worked, however this is ignoring double numbers for instance I have added to the query so that if th Reporting Month is greater than or equal to 8 however this does not work if the number is 10, 11, 12 etc... Might you have any ideas why please?

[MattAllington]

Sorry, I don't understand. Can you post your new formula?

[Adam13Hylo]

Sorry Matt, 

My formula is now: 

if Number.ToText([ReportingMonthNum]) < "8" then "FY" & Number.ToText([YearNum]) else if Number.ToText([ReportingMonthNum]) >= "8" then "FY" & Number.ToText(Value.Add([YearNum], 1)) else ""

In the formula it should be that if the "ReportingMonthNum" is greater or equal to 8 then the value should be FY & the year number plus 1, this works for months 8 & 9 however it does not work for months 10, 11 or 12:

It appears to be ignoring numbers with anything more than two digits. 

[MattAllington]

The issue is just after your second "if". You should not be using Number.ToText for the comparator, just for the concatenation. 

[Adam13Hylo]

Still does not work sorry, my query is now: 

if [ReportingMonthNum] < "8" then "FY" & Number.ToText([YearNum]) else if [ReportingMonthNum] >= "8" then "FY" & Number.ToText(Value.Add([YearNum], 1)) else ""

The Year Number column has a type of whole number as did the reporting month but I have changed this back to the default now.

Column Values

[MattAllington]

Try changing Value.Add([yearnum],1) to simply [yearnum] + 1