How to separate a column value into multiple different columns

[mglomb]

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?

[lkalawski]

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. 

[mglomb]

Is that for DAX? Can I do it in Power Query?

[lkalawski · Accepted solution]

Hi @mglomb 

This is Power Query code. Please use it and change your Source (first line). 

[mglomb]

Thank you very much!