Different values in one row

[LuisMLData]

Hi friends, I have a problem with a dataset. 

There is a column with serveral values in one row. How can I get those values into different columns ? 

This is an example: 

Id	name	Personal ID number
2	Luis	Question: Personal ID Number  Answer: 1-7 Question: English level?  Answer: adv Question: Are you looking for Work at Home or Onsite positions?  Other
3	Enrique	Question: Personal ID Number  Answer: 1-rere7 Question: English level?  Answer: advanced Question: Are you looking for Work at Home or Onsite positions?  Other"

The desire results is as follows: 

Id	name	English Level	Are you looking…	Have you …
2	Luis	7-Jan	adv	Other
3	Enrique	1-rere7	advanced	Other

[jbwtp]

Hi @LuisMLData,

there is an "Answer:" prefix for every answer except the last one. Is this a feature or you just missed the word?

Thanks,

John

[LuisMLData]

I missed the word, sorry

[jbwtp]

Then my earlier post should help to resolve it :).

[jbwtp · Accepted solution]

If this does have "Answer:" for every question then the soluiton is below:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rY4xCsJAEEWvMmwdC7UQ0kjAgIIYrSxCitWMyZLNjM7uRryNZ/FkbmdKBavPh/c/ryzVTCVqG4yLcQjovGFKYY/imLSFzQp2oT+hvJ6QkbujpDCdLOCD5tRY41qwOKBdjjBdDyMsE4QHB7DMnaEGLixwZOlAe1hzjxB7Qc54hCvHiCM3fit8i6KqpFTzaJqTmFvAX5wFBb/11nTG+v/y1Rs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, name = _t, #"Personal ID number" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", Int64.Type}, {"name", type text}, {"Personal ID number", type text}}),


   fTransform = (r)=> 
        let 
            Split = Text.Split(r[Personal ID number], "Question:"),
            Filter = List.Select(Split, each Text.Length(Text.Trim(_)) > 0),
            Split2 = List.Transform(Filter, each Text.Split(_, "Answer:")),
            Headers = Table.PromoteHeaders(Table.FromColumns(Split2))
        in
            Headers,  
    
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each fTransform(_)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {" Personal ID Number  ", " English level?  ", " Are you looking for Work at Home or Onsite positions?  "}, {" Personal ID Number  ", " English level?  ", " Are you looking for Work at Home or Onsite positions?  "})
in
    #"Expanded Custom"

[liuqi_pbi]

Thank you! This enlightens me a lot!