Forum Discussion

dvnnvd's avatar
dvnnvd
Regular Visitor
9 years ago

Help with making a PowerPivot formula work with Power BI

Hi Guys,   I currently use PowerPivot and have the below code which creates a custom column with calculated values:   =if([month_logged] >= 8,[year_logged]&"-"&right([year_logged]+1,2), [year_log...
Need Help
prakash_gautam's avatar
prakash_gautam
Resolver II
9 years ago

Hello,

You can use Text.Range and you might need Number.ToText as well. Something like:

 

if ([month_logged]) >= > then Number.ToText([year_logged]) &"-"& Text.Range(Number.ToText([year_logged]+1),2) 
 else ([year_logged])-1 &"-"& Text.Range(Number.ToText([year_logged]),2)

Thanks

dvnnvd's avatar
dvnnvd
Regular Visitor
9 years ago

prakash_gautam

 

Thank you so much for your reply. I am now much further forward.

 

Interestingly, some of the data produces the correct information but the vast majority returns "error" with the below explanation:

 

Expression.Error: We cannot apply operator & to types Number and Text.
Details:
    Operator=&
    Left=2010
    Right=-

All of my data is the same, so I'm not sure why the customised column is displaying correctly for some but not others. I have tried formatting both the columns involved in the formula to text but this makes no differance. 

  • prakash_gautam's avatar
    prakash_gautam
    Resolver II
    9 years ago

    dvnnvd

    Can you please share sample data? Myself or someone else might be able to provide an explanation.

     

    Thanks