cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Adam13Hylo
Helper I
Helper I

Custom Column Query

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.

1 ACCEPTED SOLUTION

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

 

Number.ToText([YearNum])



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

7 REPLIES 7

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

 

Number.ToText([YearNum])



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

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?

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



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

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:

PowerBi.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

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



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

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 ValuesColumn Values


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



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors