Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
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 )
Proud to be a Super User!
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 )
Proud to be a 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?
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
It's the first Google Search for 'dax text to columns' found @ https://www.nimblelearn.com/using-dax-to-split-delimited-text-into-columns/
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 | Trans | Qty | Security | Price | ||
Bought 0.222 Rpg @ 100.36 | Bought | 0.222 | Rpg | @ | 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.
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |