none
Create a list of comma-separated values from a field of a list of records RRS feed

  • Question

  • Hi Guys,

    I was trying to get help to my issue, but could not find one. I use Power Query in Excel. A have a table containing a lot of items created from a JSON produced by JIRA. Many columns are not just plain values but, in the better case, records (such as the Assignee, with their name, email address etc. stored) and, in the worse case, lists, containing records.


    Such a field is the Components field. Each issue can have multiple components, and I'd like to have a column containing all the components separated by, say, comma.


    I've created a screenshot:




    As you can see, each instance of Ticket.components is a list, and the list contains records. The record contain the field name. Instead of (or next to) the existing Ticket.components field, I'd like to see a field with values like "Client, Server".


    What's the Power Query way of doing this?



    Thanks,

    Gus 

     

    em

    Monday, October 8, 2018 4:04 PM

Answers

  • My guess is that you could do this more simply by just adding a custom column and accessing the component name from the nested records. Assuming the component name field inside each nested Ticket.components record is called Name, try adding a custom column with the following formula specified in the Custom Column dialog:

    = Text.Combine(List.Transform([Ticket.components], each [Name]), ", ")

    Ehren

    • Marked as answer by e-Musty Thursday, October 25, 2018 8:40 PM
    Wednesday, October 24, 2018 4:46 PM
    Owner

All replies

  • Hi e-Musty. Can you try adding a custom step that does something like the following?

    = Table.TransformColumns(PreviousStepName, {"Ticket.components", each Text.Combine(List.Transform(_, each Text.Combine(List.Transform(Record.FieldValues(_), Text.From), ",")), ","), type text})

    Ehren

    Tuesday, October 9, 2018 8:55 PM
    Owner
  • Dear Ehren

    Sorry for not responding earlier, I was distracted. Your suggestions works great and I can convert the values to a list. Only one last step is missing. If there are two components assigned to a ticket (that is, the field contains a list of two records), I receive a value like this:

    https://example.atlassian.net/rest/api/2/component/11454,11454,Component 1,

    https://example.atlassian.net/rest/api/2/component/11453,11453,Component 2

    (lines broken by me)

    Do you have any idea how to extract the component names themselves? I'm not aware of any built-in splitting or parsing functions that could take the 3rd, 6th, 9th etc values of a list, but there may be an easy solution to this.

    eM


    em

    Tuesday, October 23, 2018 7:43 PM
  • Can you share an example of what you want as your final output? Is it just "Component 1, Component 2" in a single column? Something else?

    You'll probably want to grab the component names from the original list, before you do the Text.Combine I shared above. Try adding a custom column and indexing into the Ticket.components field. To grab the third entry in the list, for example, you could do the following (in the Add Custom Column dialog):

    = Ticket.components{2}

    Ehren


    Tuesday, October 23, 2018 8:10 PM
    Owner
  • Hi Ehren,

    Yep, I wanted it to look like "Component 1, Component 2" and so on. And I can't believe, but I managed to figure it out myself by inserting a Text.AfterDelimiter so that it looks like this:

    #"Magic with Components" = Table.TransformColumns(#"Previous OP", {"Ticket.components", each Text.Combine(List.Transform(_, each Text.AfterDelimiter(Text.Combine(List.Transform(Record.FieldValues(_), Text.From), ","), ",", {1, RelativePosition.FromStart})), ","), type text})

    And why I'm especially happy is that I'm afraid I could not do this indexing stuff without your further help.

    Just for curiosity, is there a way to make my formula simpler? The double List.Combine + List.Transform seems superfluous, but I could not figure out how to eliminate one of those if I try to use your hint, {2} in the form of List.Transform(Record.FieldValues(_)){2} to get the third item from the list.


    em

    Wednesday, October 24, 2018 3:05 PM
  • My guess is that you could do this more simply by just adding a custom column and accessing the component name from the nested records. Assuming the component name field inside each nested Ticket.components record is called Name, try adding a custom column with the following formula specified in the Custom Column dialog:

    = Text.Combine(List.Transform([Ticket.components], each [Name]), ", ")

    Ehren

    • Marked as answer by e-Musty Thursday, October 25, 2018 8:40 PM
    Wednesday, October 24, 2018 4:46 PM
    Owner
  • This rocks! One last question: let's say I want to get the name which comes first in the alphabet. Or if there is a numeric column in place of Name, I want to get the minimum number. Then, instead of the Text.Combine function, I can apply a sorting function, but how can I get the first value in the given order?

    em

    Thursday, October 25, 2018 4:05 PM
  • You might want to try starting with your original list of records column, and then using the little double-arrow button to the right of the column name to expand the lists into new rows. Then, use the same double-arrow button to expand the records. Once that's done, you should be able to do a Group By operation and pick out the max value, min name alphabetically, etc.

    Ehren


    Thursday, October 25, 2018 4:56 PM
    Owner
  • Thanks, Ehren! Meanwhile I also figured out a formula for this:

    = Table.AddColumn(#"Operation name", "Oldest Affected Version", each try List.Min(List.Transform([Ticket Version List], each [name])) otherwise "")

    That is, I only need to replace the Text.Combine with List.Min, List.Max or, if I want a sorted list, with List.Sort.

    Thanks for your tough help, I learned a lot.

    eMusty



    em

    Thursday, October 25, 2018 8:39 PM
  • Great!
    Thursday, October 25, 2018 8:43 PM
    Owner