M expression for SSIS equivalent

Question

I've just started exploring DAX on PowerBI; so please excuse if this query sounds too novice to expert users. DAX functions are too 'different' if coming from a SQL background, hence the query.

In SSIS I'm using a function to replace values in a column based on a string (more so, an error in the value). I'm using the below to do the job easily:

Column2 = SUBSTRING([Column1],1,FINDSTRING([Column1],";#",1) - 1)

Even after looking at the Text functions on Microsoft help page.

for almost an hour trying to understand; I couldn't for some reason.

Any ideas?

I think you're confusing DAX and M. You linked to M documentation. — Alexis Olson
Thanks. Not sure what to say here; don't know what M is. Can you guide me to appropriate help link for my query? — knowone
They're both part of Power BI. M is for the query editor and DAX is for writing measures after all the data is loaded. Where are you trying to do the transformation? — Alexis Olson
I'm trying to add a new column based on the calculation in the query. On Power Query Editor, I'm trying to add a Custom Column where I should write the expression. — knowone
OK. That is M. I've added that tag for you but you should probably edit your title. — Alexis Olson

Alexis Olson Alexis Olson · Accepted Answer · 2020-05-10T06:16:13

An analogous expression in M would be

Text.Middle([Column1], 1, Text.PositionOf([Column1], ";#") - 1)

But you could also use Text.Start instead since you're starting at 1 or make it even simpler with Text.BeforeDelimiter:

Text.BeforeDelimiter([Column1], ";#")

In DAX, you'd use MID/LEFT instead of Text.Start/Text.Middle and FIND or SEARCH (depending on if you need case-sensitivity or not) instead of Text.PositionOf.

LEFT ( [Column1], SEARCH ( ";#", [Column1] ) - 1 )

Either way, the logic is nearly identical but you just have different function names.

M expression for SSIS equivalent

1 Answers