Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 😃
Solved! Go to 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.
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.
#"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! ❤️
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.