Register
Log In
Unanswered
Home
Excel Analysis Toolpak
Power Query
May 2, 2014
Offline
In the following data - I seem to have a brain freeze - I want to be able to do the following in PQ. All I want really is some pointers as to where to start
Thanks as always 🙂
1. Remove all rows with Ended in them
2. Extract all the beginning entries with * to a separate column so I'd have a * column but it could have 1, 2 or 3 asterisks.
3. Extract the middle numbers e.g. 59 <60 into a column of their own
4. Replace items like ABPm=ARTm, ABPs=ARTs and ABPd=ARTd
| * HR 59 <60 Generated at 15:20:38. |
| **ABPs 96 <100 Generated at 15:20:32. |
| **ABPs 96 <100 Ended. |
| **ABPs 96 <100 Generated at 15:20:19. |
| * HR 59 <60 Ended. |
| **ABPs 82 <100 Ended. |
| **etCO2 7.1 >6.8 Ended. |
| **etCO2 7.1 >6.8 Generated at 15:19:28. |
| * HR 142 >135 Generated at 15:19:08. |
| **ARTm 58 <60 Ended. |
| **ARTm 58 <60 Generated at 15:17:58. |
| **SpO2 89 <91 Ended. |
| **ABPs 94 <100 Generated at 15:17:41. |
| **ARTm 57 <60 Ended. |
| **ARTm 58 <60 Generated at 15:17:21. |
| * HR 59 <60 Generated at 15:16:59. |
See Answer
July 16, 2010
Offline
Hi Anne,
I've made some notes beside your list below to point you in the right direction:
1. Remove all rows with Ended in them > Text Filter using 'doe not contain'
2. Extract all the beginning entries with * to a separate column so I'd have a * column but it could have 1, 2 or 3 asterisks. > Split column; choose 'at right most delimiter'. Note: if you really want to retain all the asterisks then you'll need to add one back to the column they're split into.
3. Extract the middle numbers e.g. 59 <60 into a column of their own > Split column again using 'Generated' as the delimiter
4. Replace items like ABPm=ARTm, ABPs=ARTs and ABPd=ARTd > Replace
Here is an example file if you get stuck.
Mynda
Answers Post
Excel Analysis Toolpak
Power Query
May 2, 2014
Offline
Ah, brilliant. That's just what I wanted...just a few pointers as to where to go...Let me go and dig into this over the next couple of days. THANK YOU!
Excel Analysis Toolpak
Power Query
May 2, 2014
Offline
Finally got around to working on this. Put in a few extra bits with parsing, splitting columns, duplicating columns and got exactly what I wanted. Thank you so much for pointing me in the right direction 🙂
Most Users Ever Online: 57
Currently Online:
27 Guest(s)
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 651
Frans Visser: 210
David_Ng: 96
mey tithveasna: 71
A.Maurizio: 60
rathanak: 58
yhooithin05: 54
Anders Sehlstedt: 47
julian: 46
PaulFogel: 37
Newest Members:
Akshatraj Adig
Liesie Marais
Moet Lewis
Michelle Anderson
Veronica Gallego
krishnaraj r
Dharan Prakash Mishra
Md. Rahman
Lilian Vo
Jane Kumwenda
Forum Stats:
Groups: 2
Forums: 18
Topics: 935
Posts: 4405
Member Stats:
Guest Posters: 1
Members: 42350
Moderators: 1
Admins: 3
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea
Moderators: Genevieve Tupas
