Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Help! PowerQuery Problem (Permutation)

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 ...
Need Help
v-yalanwu-msft's avatar
v-yalanwu-msft
Community Support
3 years ago

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's avatar
    Anonymous
    Not applicable
    3 years ago
    #"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! ❤️