cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JimB-GA Member
Member

Parsing a field in DAX

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

Following is the sample table:

 

DateDescription

08/01/18Bought 1.206 Spy @ 281.0819
05/01/18Bought 1.131 Spy @ 262.8647
02/06/18Bought 40 Spy @ 264.8895
02/02/18Bought 36 Spy @ 278
02/01/18Bought 0.576 Spy @ 282.3045
12/18/17Bought 74 Spy @ 268.4641
11/15/17Bought 40 Rpg @ 102.96
11/01/17Bought 48 Rpg @ 103
11/01/17Bought 0.383 Spy @ 257.568
10/03/17Bought 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.

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisMendoza Senior Member
Senior Member

Re: Parsing a field in DAX

@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 )

View solution in original post

6 REPLIES 6
Super User
Super User

Re: Parsing a field in DAX

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?


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

ChrisMendoza Senior Member
Senior Member

Re: Parsing a field in DAX

@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 )

View solution in original post

JimB-GA Member
Member

Re: Parsing a field in DAX

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 TransQtySecurity Price
Bought 0.222 Rpg @ 100.36 Bought0.222Rpg@100.36
JimB-GA Member
Member

Re: Parsing a field in DAX

Greg,

 

You are correct in identifying the fields

 

Description TransQtySecurity Price
Bought 0.222 Rpg @ 100.36 Bought0.222Rpg@100.36

 

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

 

Thanks 

 

Jim

ChrisMendoza Senior Member
Senior Member

Re: Parsing a field in DAX

@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/

Super User
Super User

Re: Parsing a field in DAX

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.


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 283 members 3,082 guests
Please welcome our newest community members: