Forum Discussion
permutation in power query (multiple rows and same columns)
Hello,
I am trying to create a permutation in Power Query with the following data:
| Name | Country | Type |
| Jason | DE | A |
| Marry | FR | B |
| Paul | IT | C |
| US |
I am trying to get an output like this:
| Jason | DE | A |
| Jason | DE | B |
| Jason | DE | C |
| Jason | FR | A |
| Jason | FR | B |
| Jason | FR | C |
| Jason | IT | A |
| Jason | IT | B |
| Jason | IT | C |
| Jason | US | A |
| Jason | US | B |
| Jason | US | C |
| Marry | DE | A |
| Marry | DE | B |
| Marry | DE | C |
| Marry | FR | A |
| Marry | FR | B |
| Marry | FR | C |
| Marry | IT | A |
| Marry | IT | B |
| Marry | IT | C |
| Marry | US | A |
| Marry | US | B |
| Marry | US | C |
| Paul | DE | A |
| Paul | DE | B |
| Paul | DE | C |
| Paul | FR | A |
| Paul | FR | B |
| Paul | FR | C |
| Paul | IT | A |
| Paul | IT | B |
| Paul | IT | C |
| Paul | US | A |
| Paul | US | B |
| Paul | US | C |
So in a way keep the columns separated but make a match for each column 1 with column 2 and column 3 combined. Is this possible with Power Query? Thanks for the help in advance.
Anonymous , Right click on each column and Create a new list, remove duplicate and convert to table and try full outer join
refer: https://exceleratorbi.com.au/cross-join-with-power-query/
In DAX a new tbale =
crossjoin(crossjoin(distinct(Table[Name]),distinct(Table[Country])),distinct(Table[Type]))
2 Replies
- amitchandak
Super User
Anonymous , Right click on each column and Create a new list, remove duplicate and convert to table and try full outer join
refer: https://exceleratorbi.com.au/cross-join-with-power-query/
In DAX a new tbale =
crossjoin(crossjoin(distinct(Table[Name]),distinct(Table[Country])),distinct(Table[Type]))
- ERD
Community Champion
Anonymous ,
Here is one of the ways to get Cartesian product of columns within same query (it gets rid of blank values, but doesn't imply duplicates existence. If you have duplicated values, the code will be a bit different):
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8koszs9TUNJRcnEFEo5KsTrRSr6JRUWVQJ5bEJBwAgsFJJbmADmeIUDCGSwC0hMaDCQUlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Country = _t, Type = _t]), RemovedColumns = Table.RemoveColumns(Source,{"Country", "Type"}), #"Filtered Rows" = Table.SelectRows(RemovedColumns, each ([Name] <> " ")), #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Country", each Table.SelectRows( Table.FromList(Source[Country], Splitter.SplitByNothing(), null, null, ExtraValues.Error), each ([Column1] <> " "))), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Type", each Table.SelectRows( Table.FromList(Source[Type], Splitter.SplitByNothing(), null, null, ExtraValues.Error), each ([Column1] <> " "))), #"Expanded Country" = Table.ExpandTableColumn(#"Added Custom1", "Country", {"Column1"}, {"Country"}), #"Expanded Type" = Table.ExpandTableColumn(#"Expanded Country", "Type", {"Column1"}, {"Type"}) in #"Expanded Type"If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.
