Forum Discussion
Split columns and sort outcome?
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.
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.
3 Replies
- mwegener
Most Valuable Professional
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.
- mwegener
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.