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 Person...
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"
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!
