
Active Member

March 23, 2019

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

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

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.


November 8, 2013

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)
