Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
JimB-GA
Helper III
Helper III

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
ChrisMendoza
Resident Rockstar
Resident Rockstar

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





Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



View solution in original post

6 REPLIES 6
ChrisMendoza
Resident Rockstar
Resident Rockstar

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





Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



Greg_Deckler
Super User
Super User

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?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

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






Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



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

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.