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.

 

 

 

 

 

 

 

LaurentCouartou's avatar
LaurentCouartou
Icon for Solution Supplier rankSolution Supplier
9 years ago

In your expression, the then part yields a number while the else part yields a string. The data-type returned would then change depending on the context. This is not allowed in a calculated column.

 

To achieve the expected results (show nothing if your initial condition is not met), you could replace "" with BLANK()

IF( condition
      ; then
      ; BLANK()
)

or simply use the IF function with two arguments.

IF( condition
      ; then
)