none
Create a Cartesian Product with Power Query RRS feed

  • Question

  • Hi,

    Assume I have 201,202 and 203 in B2:B4 and 1,2 in range D2:D4.  I want to get the following result in range F2:G7 using Power Query

    201       1
    201       2
    202       1
    202       2
    203       1
    203       2

    In other words, how can one create a Cartesian Product using Power Query.

    Thank you.


    Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

    Saturday, August 24, 2013 12:56 AM

Answers

  • Hi Ashish,

    Please note that what Stuart is suggesting is to paste this formula into a new step, not to replace the entire query formula (which would still contain the Source step).

    This is what the full query formulas would look like. I just generated this with PQ so should work for you too (assuming you have Table1 in your workbook as Stuart described above):

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        Custom1 = let
     firstList = List.RemoveNulls(Source[first]),
     secondList = List.RemoveNulls(Source[second]),
     firstLength = List.Count(firstList),
     secondLength = List.Count(secondList),
     resultFirstList = List.Generate( () => 0, each _ < firstLength * secondLength, each _ + 1, each firstList{ Number.IntegerDivide(_, secondLength) }),
     resultSecondList = List.Repeat( secondList, firstLength)
    in
     Table.FromColumns({resultFirstList, resultSecondList})
    in
        Custom1

    Hope this helps!

    Thanks,
    M.

    Wednesday, September 4, 2013 1:49 AM

All replies

  • Given,

    first second
    201 1
    202 2
    203


    1. PowerQuery -> From Table

    2. Click fx to create new step.

    3. Paste

    = let
    	firstList = List.RemoveNulls(Source[first]),
    	secondList = List.RemoveNulls(Source[second]),
    	firstLength = List.Count(firstList),
    	secondLength = List.Count(secondList),
    	resultFirstList = List.Generate( () => 0, each _ < firstLength * secondLength, each _ + 1, each firstList{ Number.IntegerDivide(_, secondLength) }),
    	resultSecondList = List.Repeat( secondList, firstLength)
    in
    	Table.FromColumns({resultFirstList, resultSecondList})


    Sunday, August 25, 2013 12:19 AM
  • Hi,

    Thank you for replying.  That does not work.  This is the error message

    Expression.Error: The name 'Source' was not recognized.  Is it spelled correctly? Details: Name=Source, SectionName=Section1, FormulaName=Table1, FormulaPartName=null


    Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

    Monday, August 26, 2013 4:08 AM
  • Hi Ashish,

    Please note that what Stuart is suggesting is to paste this formula into a new step, not to replace the entire query formula (which would still contain the Source step).

    This is what the full query formulas would look like. I just generated this with PQ so should work for you too (assuming you have Table1 in your workbook as Stuart described above):

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        Custom1 = let
     firstList = List.RemoveNulls(Source[first]),
     secondList = List.RemoveNulls(Source[second]),
     firstLength = List.Count(firstList),
     secondLength = List.Count(secondList),
     resultFirstList = List.Generate( () => 0, each _ < firstLength * secondLength, each _ + 1, each firstList{ Number.IntegerDivide(_, secondLength) }),
     resultSecondList = List.Repeat( secondList, firstLength)
    in
     Table.FromColumns({resultFirstList, resultSecondList})
    in
        Custom1

    Hope this helps!

    Thanks,
    M.

    Wednesday, September 4, 2013 1:49 AM
  • Hi,

    Thank you.  This works well.  I am assuming that you have had to write this query yourself i.e. this query has not been generated by recording steps.  Am I right?


    Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

    Thursday, September 5, 2013 11:08 PM
  • That's correct.
    Saturday, September 7, 2013 8:32 PM
  • Hi,

    To create a Cartesian product, would one still have to write the query as shown by you above or has an option been introduced in latest updates of Power Query to accomplish this with a few click and/or minimal formulas.

    Thank you for your help.


    Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

    Monday, October 14, 2013 1:38 AM
  • Hi Ashish,

    Nothing changed in this front.

    Thanks,
    M.

    Monday, October 14, 2013 3:06 PM
  • Hi,

    Another alternative (although still not minimal) for this might be:

    let
    	Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    	SecondList = List.RemoveNulls(Source[second]),
    	ListCol = Table.AddColumn(
    		Source,
    		"ListCol", 
    		each SecondList),
        	Expand = Table.ExpandListColumn(
    		ListCol,
    		"ListCol"),
    	RemovedColumns = Table.RemoveColumns(
    		Expand,
    		"second"),
    	RenamedColumns = Table.RenameColumns(
    		RemovedColumns,
    		{"ListCol", "second"})
    in
    	RenamedColumns
    Note, while this seems to work for me, it might be a bad approach / inefficient compared to the previous answer provided, as I know very little about Power Query!
    Monday, October 14, 2013 11:18 PM
  • Hi,

    Thank you for replying.  I get the following message

    Expression.Error: The column 'second' of the table was not found. Details: second

    the heading of the second column is Second


    Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

    Monday, October 14, 2013 11:38 PM
  • I think the error you are getting might be because the formula language is case sensitive. Maybe try changing your table column header for second to all lowercase to match the query.
    Monday, October 14, 2013 11:45 PM
  • The easiest way is to have this data in 2 tables, then on one of the tables create a custom column which simply is the name of the other table. i.e.

     = Table.AddColumn(previousStep, "New Column", each otherTableName)

    you will then get the funky double arrow on this new column and clicking this will allow you to choose which columns to bring in from the other table.

    • Proposed as answer by BarryTheSprout Thursday, April 6, 2017 7:02 PM
    Wednesday, March 22, 2017 3:03 AM