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...
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]))
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.