Results 1 to 7 of 7
Like Tree2Likes
  • 2 Post By ChrisWebb

How to simplify a special combining of two lists into one

This is a discussion on How to simplify a special combining of two lists into one within the Power BI forums, part of the Question Forums category; Hi I need a code for Power Query for a combining of two lists into one Code: //it is start ...

  1. #1
    Board Regular anvg's Avatar
    Join Date
    Feb 2012
    Location
    Sankt-Peterburg
    Posts
    438

    Default 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. #2
    Board Regular
    Join Date
    Dec 2013
    Location
    Auckland, New Zealand
    Posts
    204

    Default 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. #3
    Board Regular anvg's Avatar
    Join Date
    Feb 2012
    Location
    Sankt-Peterburg
    Posts
    438

    Default 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. #4
    Board Regular
    Join Date
    Feb 2015
    Location
    Berlin
    Posts
    533

    Default 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)
    __________________________
    Imke

    Blog: www.TheBIccountant.com

  5. #5
    Board Regular anvg's Avatar
    Join Date
    Feb 2012
    Location
    Sankt-Peterburg
    Posts
    438

    Default 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. #6
    New Member
    Join Date
    Sep 2014
    Location
    Amersham, UK
    Posts
    27

    Default 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
    Ozeroth and ImkeF like this.

  7. #7
    Board Regular anvg's Avatar
    Join Date
    Feb 2012
    Location
    Sankt-Peterburg
    Posts
    438

    Default Re: How to simplify a special combining of two lists into one

    ChrisWebb, thank you very much!

    It was very informative for me.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com