Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Excel Formula to PowerBI

Hello Dears,   a short question: I was trying to use the following formula but PowerBI doesn't accept it:   Column = IF(AND(LEN(TRIM([Hilfsmittelnummer]))>=7;ISNUMBER(LEFT([Hilfsmittelnummer];2)...
Need Help
Anonymous's avatar
Anonymous
Not applicable
9 years ago

Hello Simon_Hou,

 

I am still struggling with the formula. I was trying blank() and 0 (zero) but it still answers with a error message:

 

The Value "EI" of typ "Text" can't be converted into a "Number".

I guess, in view of that, that there needs to be further advancement to the formula for the values with Letters.

 

v-sihou-msft's avatar
v-sihou-msft
Microsoft Employee
9 years ago

Anonymous

 

You may use the some funcions which can only apply on numeric field in your condition, please change the data type of that "EI" field into "Whole Number" or "Decimal Number" type.

 

Regards,

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    v-sihou-msft

     

    If i try to measure the if clause with istext(true) then blank() everything is dropped:

    i guess the problem is, there are dots between the numbers i was out for (example 23.02.02.2003 or 08.03.01.1). Those stay without the dots when i switch to whole number but i lose the first 0 like in my second example.

     

    Now i was trying to do so:

    but now i get the error " if result is a variant-typ, it can not be used for defined calculated columns".

    if(isnumber(left([Hilfsmittelnummer];2));[Hilfsmittelnummer];if(istext([Hilfsmittelnummer]);blank()))
    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      v-sihou-msft

       

      i changed my way of solving the problem and took this formula. looking for the dot on third point and so on... :D works better that way since i can't solve the other formula problems.

       

      if(mid([Hilfsmittelnummer];3;1)=".";left([Hilfsmittelnummer];2);blank())

       

       thank you all for the help! :smileyhappy: