Page 1 of 2 1 2 LastLast
Results 1 to 10 of 13

Thread: Join and Concatenate 2 Lists

  1. #1
    Conjurer Nick Burns's Avatar
    Join Date
    May 2017
    Posts
    123
    Articles
    0
    Excel Version
    Office 365

    Join and Concatenate 2 Lists



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I'm trying to concatenate two lists

    Code:
    {"A".."C"}
    {"1".."7"}
    to produce a list of
    A1
    A2
    A3
    etc..
    B1
    B2
    B3
    etc..
    C1
    C2
    C3
    etc..

    Trying to wrap my head around either List.Transform or List.Accumulate perhaps?
    Oh... by the way, YOU'RE WELCOME!

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,975
    Articles
    0
    Excel Version
    365
    Probably not the minimalist approach you're looking for but:
    Code:
    let
        a = {"A".."C"},
        b={1..7},
        OneListToTable = Table.FromList(a, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        AddCustom = Table.AddColumn(OneListToTable, "Custom", each b),
        ExpandCustom = Table.ExpandListColumn(AddCustom, "Custom"),
        Merged = Table.CombineColumns(Table.TransformColumnTypes(ExpandCustom, {{"Custom", type text}}, "en-GB"),{"Column1", "Custom"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged")[Merged]
    in
        Merged

  3. #3
    Conjurer Nick Burns's Avatar
    Join Date
    May 2017
    Posts
    123
    Articles
    0
    Excel Version
    Office 365
    Thanks - that's pretty much the solution I came up with as well - by converting to table.
    Sometimes I think I want to try something clever, which may not be the most efficient and simple is probably better (regardless of the number of steps).

    Thanks again!
    Oh... by the way, YOU'RE WELCOME!

  4. #4
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,694
    Articles
    0
    Excel Version
    Office 365 Subscription
    This is how I would have done it, too.
    Ali
    Enthusiastic self-taught user of MS Excel!

  5. #5
    Conjurer pinarello's Avatar
    Join Date
    Jun 2019
    Location
    Germany
    Posts
    129
    Articles
    0
    Excel Version
    Office 365
    It also goes a little shorter.
    PHP Code:
    let
        ListToTable  
    Table.FromList({"A".."C"}, Splitter.SplitByNothing(), nullnullExtraValues.Error),
        
    AddCustom    Table.AddColumn(ListToTable"Custom"each {1..7}),
        
    ExpandCustom Table.ExpandListColumn(AddCustom"Custom"),
        
    Merge        Table.CombineColumns(Table.TransformColumnTypes(ExpandCustom, {{"Custom"type text}}, "en-GB"),{"Column1""Custom"},Combiner.CombineTextByDelimiter(""QuoteStyle.None),"Merged")[Merged]
    in
        Merge 

  6. #6
    Super Moderator JoePublic's Avatar
    Join Date
    Sep 2011
    Location
    Askew
    Posts
    197
    Articles
    0
    Excel Version
    2010
    To just create a list, I think you could do something like this:

    Code:
    List.Transform(List.Accumulate({"A".."C"}, {}, (state, current)=>List.Combine({state, List.Zip({List.Repeat({current}, 7), {1..7}})})), each Text.Combine(List.Transform(_, Text.From)))
    This website wants to know your momentum - | Deny | | Allow |

  7. #7
    Conjurer Nick Burns's Avatar
    Join Date
    May 2017
    Posts
    123
    Articles
    0
    Excel Version
    Office 365
    Quote Originally Posted by JoePublic View Post
    To just create a list, I think you could do something like this:

    Code:
    List.Transform(List.Accumulate({"A".."C"}, {}, (state, current)=>List.Combine({state, List.Zip({List.Repeat({current}, 7), {1..7}})})), each Text.Combine(List.Transform(_, Text.From)))
    Awesome JoePublic!
    I need to study this so I can understand the logic, however I can truncate that List.Transform() part and use this:
    Code:
    = List.Transform(List.Accumulate({"A".."C"}, {}, (state, current)=>List.Combine({state, List.Zip({List.Repeat({current}, 7), {"1".."7"}})})), each Text.Combine(_))
    Thank you again!
    Oh... by the way, YOU'RE WELCOME!

  8. #8
    Conjurer Nick Burns's Avatar
    Join Date
    May 2017
    Posts
    123
    Articles
    0
    Excel Version
    Office 365
    Here are those steps done "manually" to help me understand how everything works together
    Doing this it this way makes it seem like the List.Combine() isn't needed, but it's the glue that keeps (or maintains) the [state] variable to "accumulate" and is what's returned by the List.Accumulate() function.

    Code:
    let    
        Source = "",  
        //Each line basically stands on it's own, rather than building on the previous line, to visually show how it all fits as you step through
        ListRepeatA = List.Repeat({"A"}, 7),
        ListZipA = List.Zip({List.Repeat({"A"}, 7), {"1".."7"}}),
        //This first combine maintains state
        ListCombineA = List.Combine({{}, List.Zip({List.Repeat({"A"}, 7), {"1".."7"}})}),
        ListRepeatB = List.Repeat({"B"}, 7),
        ListZipB = List.Zip({List.Repeat({"B"}, 7), {"1".."7"}}),
        // Technically the empty {} list contains the ListZipA info to maintain state
        ListCombineB = List.Combine({{}, List.Zip({List.Repeat({"B"}, 7), {"1".."7"}})}),
        ListRepeatC = List.Repeat({"C"}, 7),
        ListZipC = List.Zip({List.Repeat({"C"}, 7), {"1".."7"}}),
        // Technically the empty {} list contains the ListZipA & ListZipB info to maintain state
        ListCombineC = List.Combine({{}, List.Zip({List.Repeat({"C"}, 7), {"1".."7"}})}),
        // I use List.Union to simulate what the List.Accumulate() function returns. In truth, it returns ListCombineC as it holds all the lists.
        ListAccumulate = List.Union({ListCombineA,ListCombineB,ListCombineC}),
        //Text combine each list
        ListTransform = List.Transform(ListAccumulate, each Text.Combine(_))
    in
        ListTransform
    Oh... by the way, YOU'RE WELCOME!

  9. #9
    Conjurer Bill Szysz's Avatar
    Join Date
    Apr 2016
    Location
    Poznań, Poland Dublin, Ireland
    Posts
    125
    Articles
    0
    Excel Version
    Excel 365
    Another way
    Code:
    let
        Char = {"A".."C"},
        Num = {1..7},
        Full_List = List.Combine(List.Transform(Char, (x) => List.Transform(Num, each x & Text.From(_))))
    in
        Full_List

  10. #10
    Super Moderator JoePublic's Avatar
    Join Date
    Sep 2011
    Location
    Askew
    Posts
    197
    Articles
    0
    Excel Version
    2010
    That's a lot nicer!
    This website wants to know your momentum - | Deny | | Allow |

Page 1 of 2 1 2 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •