Extract responses to poorly formatted free text questions

[pbi_rookie01]

Hi all, long time listener, first time caller.

I need the ability to report on free text responses to poorly formatted questions in a large text field. I've searched but can't find anything suitable so hoping the community can help.

Given the structure, the only way I can think to interpret these responses is to look for any characters between each question and then interpret the result/s.

Text example below all in a single cell:

Have you done step 1: No

Is the Power BI community awesome? Y

Is the problem between keyboard and chair?

Are you sure you did step 1 - Y

For step 1 for example, I'd want to produce a column in my table called "Have you done step 1" and then populate it with a 'Yes', 'No', 'Empty' or 'Other' value depending on the characters between "Have you done step 1:" and  "Is the Power BI community awesome?".

In this case, the charcters were " No" so that should be easy enough but sometimes users may just use Y or N for example so I think the best way to categorise would be to map these based on, if empty, "Empty", else if contains Y, "Yes", else if contains N, "No" else "Other". 

Any help with this would be greatly appreciated.

[pbi_rookie01]

Turns out the extract function works perfectly and the delimiter can be the desired row content (not just the usual characters |,./- etc) and the subsequent row. Bit of work but this is exactly what i was after. 

Sharing as this may help others.

[v-xiaotang]

Hi @pbi_rookie01 

Thanks for reaching out to us.

I just want to confirm if you resolved this issue? If yes, you can accept the answer helpful as the solution or share you method and accept it as solution, thanks for your contribution to improve Power BI.

If you need more help, please let me know.

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[pbi_rookie01]

I was awaiting a reply before confirming if this solution works for me or not.

[pbi_rookie01]

Thanks for the prompt reply. I think that's what i'm looking for but I'm not clear on the actual delimiter used? Did that work on line break? If so, i think that's perfect. I'll just then have to filter to include lines with the expected text. 

[MahyarTF]

Hi,

As I understood correctly, first you want to split the cell into columns those are included the questions and answers.

Then, will decide about Y/Yes and N/No.

For the first part of power query, try to split the specific column and do as below :

Now you will see your table as below :

Replace #(#)(lf) --> #(lf) and delete the last bracket (, {"Column1.1", "Column1.2"})

Now this is the result :

You should delete the empty columns.

then do the upper function for the first column and put the delimiter as below :

Now you have a question and answer in separate columns and could handle the answer in Power query or with the Dax function on the Power BI side.

Appreciate you Kudos and please mark it as solution if it helps you