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...
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.
