Forum Discussion
Solved
How to separate a column value into multiple different columns
I have one column with the following information: {"P_Approval":"4","P_Discovery":"4","P_Docs":"4","P_Escalation":"4","P_Governance":"4","P_Schedule":"5"}
I need to separate all the values in the column above into different columns in a way that shows:
| Approval | Discovery | Docs | Escalations | Governance | Schedule |
| 4 | 4 | 4 | 4 | 4 | 5 |
All the numbers after the name change in each row.
What is the best way to do that?
4 Replies
Hi mglomb ,
To transform from this form:
to this:
you can use these 4 steps:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wqo5RCoh3LCgoyi9LzIlRsopRMolR0gEJumQWJ+eXpRZVoormJxejCLgWJyfmJJZk5uehCLuDtOYl5iWnoggHJ2ekppTmQARNY5RqlWJ1sDnCAqsjTNEdYUqSI4ywOcIS7IhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}), #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByDelimiter("""P_", QuoteStyle.None), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6", "Column1.7"}), #"Removed Other Columns" = Table.SelectColumns(#"Split Column by Delimiter",{"Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6", "Column1.7"}), #"Extracted Text Between Delimiters" = Table.TransformColumns(#"Removed Other Columns", {{"Column1.2", each Text.BetweenDelimiters(_, """", """", 1, 0), type text}, {"Column1.3", each Text.BetweenDelimiters(_, """", """", 1, 0), type text}, {"Column1.4", each Text.BetweenDelimiters(_, """", """", 1, 0), type text}, {"Column1.5", each Text.BetweenDelimiters(_, """", """", 1, 0), type text}, {"Column1.6", each Text.BetweenDelimiters(_, """", """", 1, 0), type text}, {"Column1.7", each Text.BetweenDelimiters(_, """", """", 1, 0), type text}}), #"Renamed Columns" = Table.RenameColumns(#"Extracted Text Between Delimiters",{{"Column1.2", "Approval"}, {"Column1.3", "Discovery"}, {"Column1.4", "Docs"}, {"Column1.5", "Escalations"}, {"Column1.6", "Governance"}, {"Column1.7", "Schedule"}}) in #"Renamed Columns"If you have any other questions feel free to ask.
