locked
Combine two unique lists into a table RRS feed

  • Question

  • Hi,

    I've been searching but haven't found the exact answer. I"m looking to combine two unique lists/tables into one table in PQ.

    Example:

    Lists are in different worksheets

    List1
    a
    b
    c

    List2
    1
    2
    3

    Combined List
    List1 List2
    a 1
    b 1
    c 1
    a 2
    b 2
    c 2
    a 3
    b 3
    c 3

    Is there a way to reference the entire list or column. I've only seen manually creating a list typing "a, 1" kind of formula, however my lists are very long.

    Any help would be greatly appreciated!

    Tuesday, November 21, 2017 6:42 PM

Answers

  • Hi EdCo7,

    Thanks for your reponse.

    As I test , the solution in below link and the power query code provided by Darren should work for you.

    https://exceleratorbi.com.au/cross-join-with-power-query/

    Please kindly mark the helpful replies as answers. By doing so, it will benefit all community members who are facing similar issues. Your contribution is highly appreciated.


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by AV111Editor Thursday, January 18, 2018 11:37 AM
    Thursday, November 23, 2017 6:34 AM

All replies

  • CREATE  TABLE List1 (l1 VARCHAR(1))
    INSERT INTO List1 VALUES('a'),('b'),('c')
    
    CREATE  TABLE List2 (l2 INT)
    INSERT INTO List2 VALUES (1),(2),(3)
    
    SELECT l1,l2 FROM List1 CROSS JOIN List2


    Please mark as answered, If you feel happy with this answer.

    Tuesday, November 21, 2017 6:48 PM
  • Sorry I didn't see it is about power pivot.

    You can do the cross join in the power pivot as explained in this article :

    https://msdn.microsoft.com/en-us/library/gg492168.aspx?f=255&MSPPError=-2147217396


    Please mark as answered, If you feel happy with this answer.

    Tuesday, November 21, 2017 6:53 PM
  • Thank you for your quick reply. I'm actually looking to do it in Power Query. I have 2 lists that are converted into tables and loaded into Workbook Queries as connection only.

    It would be great to create a new query that combines the two unique lists as illustrated in the original request without having to manually type in the values. The lists are very long and frequently change.

    I appreciate all the help.


    Tuesday, November 21, 2017 8:39 PM
  • You can just do a Table.AddColumn to add one of the tables to each row of the other. Then use Table.ExpandTableColumn to generate the exploded data set. You should be able to use a query like the following, just replace the hard coded example data in the Table1 and Table2 variables with references to your existing "connection only" queries

    eg.

    let
        Table1 = Table.FromList({1,2,3}, Splitter.SplitByNothing(), null, null),
        Table2 = Table.FromList({"a","b","c"}, Splitter.SplitByNothing(), null, null),
        Crossjoin = Table.AddColumn(Table1, "T2", each Table2),
        ExpandT2 = Table.ExpandTableColumn(Crossjoin, "T2", {"Column1"}, {"Column2"})
    in
        ExpandT2


    http://darren.gosbell.com - please mark correct answers

    Tuesday, November 21, 2017 10:05 PM
  • Hi EdCo7,

    Thanks for your question.

    For this issue, you can refer to below blog talking about cross join with power query:
    https://exceleratorbi.com.au/cross-join-with-power-query/

    According to your description, your problem is more related to Power Query. Since our forum is discussing Power Pivot issue, to solve your question more efficiently, please post your question in Power Query forum: https://social.technet.microsoft.com/Forums/en-us/home?forum=powerquery ,you will get a more professional support from there, thank you for your understanding and support.


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, November 22, 2017 6:51 AM
  • I didn't realize it was in the wrong forum. My apologize.
    Wednesday, November 22, 2017 4:46 PM
  • Hi EdCo7,

    Thanks for your reponse.

    As I test , the solution in below link and the power query code provided by Darren should work for you.

    https://exceleratorbi.com.au/cross-join-with-power-query/

    Please kindly mark the helpful replies as answers. By doing so, it will benefit all community members who are facing similar issues. Your contribution is highly appreciated.


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by AV111Editor Thursday, January 18, 2018 11:37 AM
    Thursday, November 23, 2017 6:34 AM
  • Hi EdCo7,

    Any update ?I am marking answer of  wilson.


    https://social.technet.microsoft.com/wiki/contents/articles/37872.sql-server-installation-on-centos-linux.aspx

    Thursday, January 18, 2018 11:37 AM
    Answerer