none
Repeat An Entire Table for Each Row of Another Table RRS feed

  • Question

  • I could probably write a VBA loop for this, but I just feel like PowerQuery/Get&Transform would do the job better/smarter/faster. ... if I know how to do it.

    I have a table of accounts (a few thousand) and another table of 5 rows that I want to repeat for each account, aggregating it all into a single table.

    The two tables have no relationship themselves, but I do need to combine data from the first when I append it to my output.

    Here's a screenshot that better shows what I'm trying to do.  I'm not positive that this is even possible with Get & Transform / PowerQuery, but I feel like it'd be faster than writing my own VBA loop to do it.

    Link to full-size image: https://social.technet.microsoft.com/Forums/getfile/1242828


    Shawn Keene


    • Edited by Shawn 'Cmdr' KeeneMVP Wednesday, March 21, 2018 6:07 PM added link to see image in larger view
    Wednesday, March 21, 2018 6:01 PM

Answers

All replies

  • You are looking for a cross join between Accounts and Info table.

    In Power Query say if you have the Accounts table and Info table already loaded you can add an additional step to the Accounts query.

    =Table.AddColumn(Accounts, "More", each Info)

    Then expand the "More" column to get nearly the desired outcome.

    For some other details see
    https://exceleratorbi.com.au/cross-join-with-power-query/
    Wednesday, March 21, 2018 7:24 PM
  • I'm going to try this out soon.  Yesterday I ended up writing a short single-purpose VBA macro to loop down the account list and generate the output rows for each one, pasting them into a single table.  Got the job done, but I'm certainly hoping to use query instead soon.


    Shawn Keene

    Thursday, March 22, 2018 1:53 PM
  • That's very procedural. Power Query on the other hand is more declarative and functional.

    You're more than welcome to join the club :)

    Thursday, March 22, 2018 6:42 PM
  • Certainly.  I've used PowerQuery and Power BI to get data or send it to dashboards, but this particular one-off case of needing to build a list, I just needed to get it done fast so I could upload it to Google asap, but I will absolutely check out this type of join.

    Shawn Keene

    Friday, March 23, 2018 1:49 PM
  • Hi Shawn,

    Did you get a chance to try Igor's solution?


    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com, Twitter: @nimblelearn

    Wednesday, March 28, 2018 4:28 PM
    Moderator