Parsing a field in DAX

[JimB-GA]

Hello.  Still a newbie and trying to parse a field into new columns.

Following is the sample table:

DateDescription

08/01/18	Bought 1.206 Spy @ 281.0819
05/01/18	Bought 1.131 Spy @ 262.8647
02/06/18	Bought 40 Spy @ 264.8895
02/02/18	Bought 36 Spy @ 278
02/01/18	Bought 0.576 Spy @ 282.3045
12/18/17	Bought 74 Spy @ 268.4641
11/15/17	Bought 40 Rpg @ 102.96
11/01/17	Bought 48 Rpg @ 103
11/01/17	Bought 0.383 Spy @ 257.568
10/03/17	Bought 0.261 Vcr @ 143.92

I would light to parse the description field into the security purchased, quantity, and price.  I can do this in Excel, but DAX eludes me.  Can someone help?  Very much appreciated.

[ChrisMendoza · Accepted solution]

@JimB-GA,

You could try this kind of pattern:

Column = 

// Gets position of 1st blank 'space'
VAR searchBlankSpace = SEARCH ( " ", yourTable[Description], 1 )

// Gets position of @ symbol
VAR searchAt = SEARCH ( "@", yourTable[Description], 1 )

VAR getLengthBetween = searchAt - searchBlankSpace

RETURN MID ( yourTable[Description], searchBlankSpace + 1, getLengthBetween - 1 )

[Greg_Deckler]

Parsing should be nearly identical between Excel and DAX, Can you explain how the field gets parsed or post the Excel formulas?

You can do this  in M or DAX. For the first line, I imagine that 1.206 is the quantity, Spy is the security purchased and 281.0819 is the price? Can you confirm?

[JimB-GA]

Greg,

You are correct in identifying the fields

Description		Trans	Qty	Security		Price
Bought 0.222 Rpg @ 100.36		Bought	0.222	Rpg	@	100.36

In Excel I use data tools to turn text to columns with SPACE is the delimeter.

Thanks 

Jim

[ChrisMendoza]

@JimB-GA,

It's the first Google Search for 'dax text to columns' found @ https://www.nimblelearn.com/using-dax-to-split-delimited-text-into-columns/

[JimB-GA]

In Excel I just parse the field using data tools->Delimited-> text to columns with space as teh delimeters.

You are correct.  The first is quantity, second is the security, and the last is price.

Description		Trans	Qty	Security		Price
Bought 0.222 Rpg @ 100.36		Bought	0.222	Rpg	@	100.36

[Greg_Deckler]

I would personally do this in the Query Editor instead of DAX. You can just use the Split Column from the ribbon to parse into separate fields based upon a space character. Then just remove the column with the @ symbol.