Forum Discussion
Prepare data for analyzing
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
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!
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.
3 Replies
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!
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.
Hello, guys. Thanks for your help.
