Prepare data for analyzing

[Milan]

Hello community, 

i have issue with preparing data for analyzing combination of them.

I have excel/csv table with 1 column and approximately 1500 rows. And would like to make table with two columns. It will be combination of all options. Thats 1500x1500 = 2 250 000 rows.

I make simple example with 3 rows, thats mean 9 combinations. 

I think so the best way to do that is in Power Query.

Does anybody have any idea how to do that?

Thanks for your help and time

Milan

[m13eam · Accepted solution]

Hi - yes, that is possible in PowerQuery:

• Load your Excel file containing the 1500 rows into PowerQuery
• At the end of that query, add a "Custom Column"
• Enter a formula, referencing the previous step. For example, if the previous step in the query was "Changed Type", you would enter: 

= #"Changed Type"​

See my example, where my previous step was "Renamed Columns":

• Expand the new column and you should now have all the combinations in a 2 column table!

I hope that helps. Let me know if any steps were unclear.

If this solved the issue, please mark my post as a solution.

Thanks!

[Milan]

Hello, guys. Thanks for your help.

[m13eam · Accepted solution]

Hi - yes, that is possible in PowerQuery:

• Load your Excel file containing the 1500 rows into PowerQuery
• At the end of that query, add a "Custom Column"
• Enter a formula, referencing the previous step. For example, if the previous step in the query was "Changed Type", you would enter: 

= #"Changed Type"​

See my example, where my previous step was "Renamed Columns":

• Expand the new column and you should now have all the combinations in a 2 column table!

I hope that helps. Let me know if any steps were unclear.

If this solved the issue, please mark my post as a solution.

Thanks!

[AlexisOlson · Accepted solution]

Yep. This is what MS documentation suggests too.

https://learn.microsoft.com/en-us/power-query/cross-join

@Milan Just FYI, this type of transformation is called a "cross join" or a "Cartesian product" of a table/list with itself.