Log In
Register
Home
Active Member
March 23, 2019
Offline
Hi,
I have text that was output from a database at some point that I would like to get into tabular format using powerquery. I have two problems that I can't seem to get around:
(1) the text is in linear format with the field name, a tab, and then the field value---how do I get that into columns?
(2) Here's the tricky part, the "post_text" tag is strangely mixed up in it's own field values. Fortunately the field value is contained by special characters "Œ". What makes this especially challenging, as you will see in the sample below, the text strangely breaks rows idiosyncratically. In other words, the values for the "post_text" column for the three records below would be:
"There is an issue with the back side fuse post_text box that is misbehaving"
"Port side copling no long tight around back side H joint, must change post_text out looped bolt"
"Back side power relay also failing based on meter readings and power flow indicator"
Any ideas? Sample data below.
Œticket_doc 738473 created_date 8/20/2011 post_id 235051 post_date 8/20/2011 problem_type Electrical ticket_owner_user John James ticket_ owner_ contact j@jay.com Œ There is an issue with the back side fuse post_text box that is misbehaving Œticket_doc 738422 created_date 8/20/2011 post_id 235051 post_date 8/20/2011 problem_type Mechanical ticket_owner_user John James ticket_ owner_ contact j@jay.com Œ Port side copling no long tight around back side H joint, must change post_text out looped bolt Œticket_doc 738413 created_date 2/20/2013 post_id 235051 post_date 2/20/2012 problem_type Electrical ticket_owner_user John James ticket_ owner_ contact j@jay.com Œ Back side power relay also failing based on meter readings and power flow indicator
November 8, 2013
Offline
Hi Todd,
Can you please upload a sample text file as it is exported from that database? Pasted data in browser will alter the data, the browser is converting some chars into html formats.
Active Member
March 23, 2019
Offline
I apologize. Please see attached. And thank you!
November 8, 2013
Offline
Hi Todd,
Before each group, there is a description like:
"
There is an issue with the back side fuse post_text box that is misbehaving
"
This example description is split over 2 rows, while the last one is split over 3 rows. Because the data is not in a single row as expected, and it's not even in a fixed number of rows, makes things more complicated. If we remove descriptions, things are much easier, see the attached file.
November 8, 2013
Offline
Descriptions needs to be combined into a single line of text in this case.
This can be done fairly easy, by filling down the ticket doc ID to all rows, keep only descriptions then group and join the text with the same ID.
Last step will be to merge the description with the previous table, using the ID and the lookup key.
Sample attached.
Active Member
March 23, 2019
Offline
Thank you very much for your generous help! I'm running into an error with the "filled up" step. I'm using your example file, and the only thing I changed was the text source. Any idea?
Thank you again.
November 8, 2013
Offline
Take a look at the previous step, just above Filled Up step. There should be a column named "ticket_doc", the next step query says it cannot find that column.
Originally, you had "Œticket_doc", make sure that symbol is eliminated at Replaced Value step, otherwise you will have to check what symbol you have there and remove it.
1 Guest(s)
