Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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"
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
I missed the word, sorry
Then my earlier post should help to resolve it :).
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
53 | |
21 | |
13 | |
11 |