Thanks:  0
Likes:  0

# Thread: How to simplify a special combining of two lists into one

1. ## How to simplify a special combining of two lists into one

Hi
I need a code for Power Query for a combining of two lists into one
Code:
```//it is start lists with equal length
listA = {"a","b","c"},
listD = {"1","2","3"},
//it is a needed list
listAD = { {"a","1"},{"b","2"},{"c","3"} }```
I created a query for this task. It is
Code:
```let
//start lists A and D
listA = {"a","b","c"},
listD = {"1","2","3"},
//convert list to tables
tableA = Table.FromList(listA),
tableD = Table.FromList(listD),
//add enumeration column
indexA = Table.AddIndexColumn(tableA,"IdA"),
indexD = Table.AddIndexColumn(tableD,"IdD"),
//change Column1 name to another for a join
renameA = Table.RenameColumns(indexA,{ {"Column1","A"} }),
renameD = Table.RenameColumns(indexD,{ {"Column1","D"} }),
//join tables by enum columns
join = Table.Join(renameA,"IdA",renameD,"IdD")[ [A],[D] ],
//create and return a needed listAD = { {"a","1"},{"b","2"},{"c","3"} }
return = Table.AddColumn(join,"combined",each {[A],[D]})[combined]
in
return```
Is there any way to do my code more simple?
I understand that I can write an udf-function for steps to a join command like this
Code:
```let
Creator = (inList as list,suffix as text) as table =>
let
toTable = Table.FromList(inList),
changeName = Table.RenameColumns(toTable,{ {"Column1",suffix} }),
indexCol = Table.AddIndexColumn(changeName,"Id" & suffix)
in
indexCol
in
Creator```
But I interest other solve, maybe with list functions.
Many thanks!

2. ## Re: How to simplify a special combining of two lists into one

Here are two ideas :

Code:
```let
listA = {"a","b","c"},
listD = {"1","2","3"},
listAD = Table.ToRows(Table.FromColumns({listA,listD}) )
in
listAD```
Code:
```let
listA = {"a","b","c"},
listD = {"1","2","3"},
listAD = List.Generate(
()=> [ Position = 0, ItemA = listA{0} , ItemD = listD{0} ],
each [Position] < List.Count(listA),
each [ Position = [Position]+1, ItemA=listA{[Position]+1}, ItemD = listD{[Position]+1} ],
each {[Item1], [Item2]}
)

in
listAD```

3. ## Re: How to simplify a special combining of two lists into one

Excelent! Thank you very much!

each {[Item1], [Item2]} is necessary to correct on {[ItemA], [ItemD]}.

Could I ask you another question? What other iteration varinats has Power Query?

4. ## Re: How to simplify a special combining of two lists into one

There are no "out-of-the-box"-Iteration methods in Power Query, but you can build them:

1) Create Running Totals in Power QueryThe Ken Puls (Excelguru) Blog
2) Using List.Generate() To Make Multiple Replacements Of Words In Text In Power Query | Chris Webb's BI Blog

Performance-tuning is essential when working with larger datasets (using List.Buffer or Table.Buffer)

5. ## Re: How to simplify a special combining of two lists into one

Your first link I have already read. But with than "Create Running Totals" solution is one problem. It's difficulty is O(N^2). Your solution and other in your second link are more interesting, because to allow solving that task with O(N). I will try.

Thank you very much for your collaboration!

6. ## Re: How to simplify a special combining of two lists into one

Here's another option, a bit simpler than using List.Generate():

Code:
```let
listA = {"a","b","c"},
listD = {"1","2","3"},
listpositions = List.Positions(listA),
output = List.Transform(listpositions, each {listA{_}, listD{_}})
in
output```
Chris

7. ## Re: How to simplify a special combining of two lists into one

ChrisWebb, thank you very much!

It was very informative for me.

## User Tag List

#### Posting Permissions

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