none
How to combine a list of lists when some "lists" have only 1 item RRS feed

  • Question

  • I have a list of lists (MyList) where each "list" is comma-separated and of varying length, e.g.

    • A,B,C,D,E
    • X
    • B,C,F
    • A,X

    I need to combine all the items together (Step 1), to output the following list: A,B,C,D,E,X,B,C,F,A,X

    And then (Step 2), output the distinct list, preferably alphabetically sorted: A,B,C,D,E,F,X

    Step 2 is no problem but I'm having a problem combining the lists. e.g. List.Combine(MyList) results in an error, with the 2nd item (X), not actually being a list as it only has one item.

    I'm wondering if someone would have an elegant solution for this?

    Friday, August 25, 2017 4:24 PM

Answers

  • Hey Dustin,

    Just like Colin said, Text.ToList is the function to use in this case. You could use other functions, but in the end what you'd want to do is simply transform that value that is not like the rest to look exactly like the rest. If it wasn't a table, then transform that to a table, if it wasn't a record, then transform that to a record, but since its a list, then transform that value to a list.

    Here's a simple implementation of that:

    let
        Source = { {"A","B","C","D","E"},
    "X",{"B","C","F"},
    {"A","X"}},
        Custom1 = List.Transform(Source, (_)=> if Value.Type(_)<> List.Type then {_} else _),
        Custom2 = List.Combine(Custom1),
        Custom3 = List.Distinct(Custom2)
    in
        Custom3


    • Edited by Miguel Escobar Saturday, August 26, 2017 11:32 AM grammar!
    • Marked as answer by Dustin007 Saturday, August 26, 2017 11:55 AM
    Saturday, August 26, 2017 11:32 AM
  • How are the lists derived in the first place? If by some transformation, then it should be possible to have X as a list with a single item. Otherwise, you may have to do the conversion manually before combining e.g. {"X"}, or Text.ToList("X").
    • Marked as answer by Dustin007 Saturday, August 26, 2017 11:55 AM
    Friday, August 25, 2017 4:39 PM

All replies

  • How are the lists derived in the first place? If by some transformation, then it should be possible to have X as a list with a single item. Otherwise, you may have to do the conversion manually before combining e.g. {"X"}, or Text.ToList("X").
    • Marked as answer by Dustin007 Saturday, August 26, 2017 11:55 AM
    Friday, August 25, 2017 4:39 PM
  • Thanks Colin, the original source is a column in a CSV file. Some rows have only one item in that column, but most have multiple items wrapped in quotes and comma-separated.

    You put me on the right track with the Text.ToList, however Text.Split was what I was looking for.

    Thanks again

    Friday, August 25, 2017 5:41 PM
  • Hey Dustin,

    Just like Colin said, Text.ToList is the function to use in this case. You could use other functions, but in the end what you'd want to do is simply transform that value that is not like the rest to look exactly like the rest. If it wasn't a table, then transform that to a table, if it wasn't a record, then transform that to a record, but since its a list, then transform that value to a list.

    Here's a simple implementation of that:

    let
        Source = { {"A","B","C","D","E"},
    "X",{"B","C","F"},
    {"A","X"}},
        Custom1 = List.Transform(Source, (_)=> if Value.Type(_)<> List.Type then {_} else _),
        Custom2 = List.Combine(Custom1),
        Custom3 = List.Distinct(Custom2)
    in
        Custom3


    • Edited by Miguel Escobar Saturday, August 26, 2017 11:32 AM grammar!
    • Marked as answer by Dustin007 Saturday, August 26, 2017 11:55 AM
    Saturday, August 26, 2017 11:32 AM
  • That's great, thanks Miguel. I did have an issue when using Text.ToList in that when using it in context, it was splitting an item into individual characters. In the context of this post however, its fine.

    Thanks also for the type checking which I'm sure will come in useful in future.

    I also found another solution by using Text.Split prior to convert my table column to a list of lists.

    Thanks again

    Saturday, August 26, 2017 11:55 AM
  • Yup! the Text.ToList is pretty specific for the case at hand where you only have text values.

    However, the code that I posted uses {_} which will simply put whatever value you have into a List. Apart from that, the most important part of the whole code is the checking of the Type with Value.Type.

    Glad that you found a solution to your situation.

    Best!

    Saturday, August 26, 2017 1:04 PM
  • Hi Miguel :-)

    We can also check the type of value in this way (below)

    Custom1 = List.Transform(Source, each if _ is list then _ else {_}),
    Cheers :-)


    • Edited by Bill Szysz Saturday, August 26, 2017 1:30 PM
    Saturday, August 26, 2017 1:30 PM
  • That looks CLEAN, Bill!

    Just to keep my original places inside my if 

    = List.Transform(Source, each if not ( _ is list) then {_} else _)
    I like your use of the tokens/keywords.

    Saturday, August 26, 2017 6:07 PM