Split columns and sort outcome?

[Anonymous]

Hi all,

I'm trying to separate a column which has an array of key/values in it, and the keys are not always:

ColumnTags

{"App: App1","Build: Build1", "Environment: Dev","Run Time: 24x7"}

{"App: App2","Environment: QA", "Run Time: 24x7"}

{"Build: Build2","Run Time 24x7"}

First of all, when I split the columns by the comma, the first output creates only two columns, and selects the first two key/value pairs. Everything else gets deleted.

I have to specify the amount of columns I want to be certain no values are getting deleted.

Is this expected behaviour? Am I doing something wrong? Or is it a bug?

Second, and this makes sense, the columns are sequencial and are filled in the same order the array is listed, so if in the first column, the first row key/value is App, and in the second is Build, then it will look like this:

Column1	Column2	Column3	Column4
App: App1	Build: Build1	Environment: Dev	Run Time: 24x7
App: App2	Environment: QA	Run Time: 24x7	$null
Build: Build2	Run Time: 24x7	$null	$null

The $null values are expected and don't disrupt anything for me, but I do want to sort the values. For example, I want column 1 only to be App, and column 2 only be Build

Is this possible? How so?

Lastly, I want to know if there's any way to select only certain values from the array and use only those as columns? Say I want to use only App and Build and discard everything else. How can I do that?

Thanks in advance.

[mwegener]

Hi @Anonymous,

try this:

let
Source = Table.FromColumns({Lines.FromBinary(File.Contents("C:\Test.txt"))}),
#"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1),
#"Extracted Text Between Delimiters" = Table.TransformColumns(#"Added Index", {{"Column1", each Text.BetweenDelimiters(_, "{", "}"), type text}}),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Extracted Text Between Delimiters", {{"Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1", type text}}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Column1.1", "Column1.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Column1.1", type text}, {"Column1.2", type text}}),
#"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[Column1.1]), "Column1.1", "Column1.2")
in
#"Pivoted Column"

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

[Anonymous]

Thank you for the help.

Could you help me understand your Query so I know what to replace where and what am I doing?

Thanks again

[mwegener · Accepted solution]

Hi @Anonymous 

first remove the curly braces

Add a Index (id)

Split Column by Comma into Rows (One Row for each Key:Value pair)

Split Column by Colon (Split Key:Value pair)

Pivot Column "Column1.1" -> Value Column "Column1.2" -> Don't Aggregate

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.