# Create a Cartesian Product with Power Query

• ### 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

• 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.

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]),
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 Thursday, April 6, 2017 7:02 PM
Wednesday, March 22, 2017 3:03 AM