Join and Concatenate 2 Lists

Nick Burns

Member
Joined
May 24, 2017
Messages
162
Reaction score
0
Points
16
Excel Version(s)
Office 365
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?
 
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
 
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! :)
 
It also goes a little shorter.
Code:
let
    ListToTable  = Table.FromList({"A".."C"}, Splitter.SplitByNothing(), null, null, ExtraValues.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
 
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)))
 
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!
 
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
 
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
 
That's a lot nicer! :)
 
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
Bill - you are the man. I've now been using PQ since 2017 and I still have trouble with these in-line functions. But once I see them, I can usually understand them.

Forgoing the initial variable setup, and changing the num list to use a text list of numbers, I can get it down to one line and remove the Text.From() function.
Code:
= List.Combine(List.Transform({"A".."C"}, (x) => List.Transform({"1".."7"}, each x & _)))
 
Here is my use case for this: I have to split up the days of a timecard due to union rate changes, so the first part of the week will 0 out the second half of the week and the second part will 0 out the first half.
Where it needs to "split" is based on a parameter.

Here are the 3 queries.
Code:
// parmSplitDay
4 meta [IsParameterQuery=true, Type="Number", IsParameterQueryRequired=true]


// Split1
let
    Source = List.Combine(List.Transform({"RTDay_","OTDay_","DTDay_"}, (x) => List.Transform({Number.ToText(parmSplitDay + 1).."7"}, each x & _))),
    List1 = List.Zip({Source,List.Transform({1..List.Count(Source)}, each each 0),List.Transform({1..List.Count(Source)}, each type number )})
in
    List1


// Split2
let
    Source = List.Combine(List.Transform({"RTDay_","OTDay_","DTDay_"}, (x) => List.Transform({"1"..Number.ToText(parmSplitDay)}, each x & _))),
    List2 = List.Zip({Source,List.Transform({1..List.Count(Source)}, each each 0),List.Transform({1..List.Count(Source)}, each type number )})
in
    List2
The two lists are used in queries to do column transforms.
These queries are concise, but I have a feeling that the transformations (List.Zip) can be incorporated into the the List.Combine logic??
 
Eureka!
See I just need the basis of it to understand how it works, so now I can get everything I need in one function:

Code:
// parmSplitDay
4 meta [IsParameterQuery=true, Type="Number", IsParameterQueryRequired=true]


// Split1
let
    Source = List.Combine(List.Transform({"RTDay_","OTDay_","DTDay_"}, (x) => List.Transform({parmSplitDay + 1..7}, each {x & Text.From(_),each 0, type number})))
in
    Source


// Split2
let
    Source = List.Combine(List.Transform({"RTDay_","OTDay_","DTDay_"}, (x) => List.Transform({1..parmSplitDay},  each {x & Text.From(_),each 0, type number})))
in
    Source
 
Back
Top