Convert Excel Formula to DAX compantible

[dek8ce]

I'm relatively new to PowerPivot and I'm looking to push a large dataset to PowerPivot and would then like to perform various formulas in the background.

I have a relatively intricate excel formula that pulls purchase order information out of a general ledger transaction description. However, sometimes it's there and sometimes it's not. Therefore, I'm getting some weird errors popping up when pushing this formula to DAX. I was wondering if you all could give me a hand in converting it over...

=IF(ISERROR(MID([@Text], (SEARCH("sbb", [@Text])), 10)), (MID([@Text], (SEARCH("hpe", [@Text])), 10)), MID([@Text], (SEARCH("sbb", [@Text])), 10))

It searches for a string (all our POs start with HPE or SBB and are 10 characters long), if there, it returns the next ten characters, and if not leaves it blank. It's the if blank part that I think is holding me up. Ideas?

 

[gazpage]

I assume you are creating a calculated column? If so, I'd do it in power query instead. Pull the data in and use the wizard to add a conditional column, keep adding further 'else' conditions as needed.