Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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: A...
Need Help
  • mwegener's avatar
    mwegener
    6 years ago

    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.

mwegener's avatar
mwegener
Icon for Most Valuable Professional rankMost Valuable Professional
6 years ago

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's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      mwegener
      Icon for Most Valuable Professional rankMost Valuable Professional
      6 years ago

      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.