Help! PowerQuery Problem (Permutation)

[Anonymous]

Hi everyone, i hope you are doing well...

My problem: I need to know the number of iterations between teams that worked on the same epic.

My data is something like:

Epic Name	Team
A	X
A	Y
A	Z
B	X
B	Y
C	Z
C	Y

For example:
Epic A i have three teams working together, so the possibilities are: XY, XZ, YX,YZ, ZX, ZY.

Epic B: XY, YX.

Epic C: ZY, YZ

The result would/could be something like (considering the 3 epics above) - this is the output that i want but idk how to do that

Team	x	y	z
x	----	2	1
y	2	----	2
z	1	2	----

If anyone solve this problem, could you please send me the editor script ?! that would be great 😃

[v-yalanwu-msft · Accepted solution]

Hi, @Anonymous ;

Try it.

let
    Source = Table.NestedJoin(Data, {"Epic Name"}, Data1, {"Epic Name"}, "Data1", JoinKind.LeftOuter),
    #"Expanded Data1" = Table.ExpandTableColumn(Source, "Data1", {"Team"}, {"Data1.Team"}),
    #"Added Conditional Column" = Table.AddColumn(#"Expanded Data1", "Custom", each if [Team] = [Data1.Team] then 1 else null),
    #"Filtered Rows" = Table.SelectRows(#"Added Conditional Column", each ([Custom] = null)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom"})
in
    #"Removed Columns"

The final show:

Solved: Generating row value combinations - Microsoft Power BI Community

Permutations table in Power BI or Power Query in Excel – The BIccountant

Solved: Permutations and Combinations - Microsoft Power BI Community
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

#"Coluna em pivô" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Data1.Team]), "Data1.Team", "Epic Name", List.Count),
#"Colunas Reordenadas" = Table.ReorderColumns(#"Coluna em pivô",{"Team", "X", "Y", "Z"})

Thanks, your script + script above i can achieve the result wanted! ❤️