Forum Discussion
Solved
Different values in one row
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 |
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"
5 Replies
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
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"Thank you! This enlightens me a lot!
I missed the word, sorry
Then my earlier post should help to resolve it :).
