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
Columns = IF(LEN(TRIM([Hilfsmittelnummer]))>6&&ISNUMBER(LEFT([Hilfsmittelnummer];2)*1)&&ISNUMBER(INT(MID([Hilfsmittelnummer];4;1)));LEFT([Hilfsmittelnummer];2)*1;"")

Hello Laurent,

 

I have changed the formula, but know I receive the message that the datatyp variant, can't be used for the definition of calculating columns. (sorry if some meanings sound strange or irregular, I am not a native speaker).

 

A short Examaple: bottom is the regular and below is the irregular stuff. The Target is to cut the regulars until the first 2 numbers and drop the irregulars.

 

 

 

 

 

 

 

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

Anonymous

 

As your error message said, your formula returns a dynamic data type. You can only keep one data type. Please replace your empty string ("") with either BLANK() or 0.

 

Regards,

  • 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
      Icon for Microsoft Employee rankMicrosoft 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()))