Forum Discussion
Grouping by?
Hello
could anyone please help me with following?
I have below table and I would need to make pairs of Codes by column Name - is there a way how to do it in Power BI??
example - I would need a result of a new table where first column would be Name and in the second column combination of 2 Codes:
Thank you very much!
ORIGINAL TABLE:
| Code | Name |
| Code1 | Name1 |
Code2 | Name1 |
| Code3 | Name1 |
Code1 | Name2 |
Code2 | Name2 |
| Code2 | Name3 |
| Code3 | Name3 |
| Code5 | Name3 |
| Code6 | Name3 |
NEW TABLE:
| Code | Name |
| Code1 + Code2 | Name1 |
Code1 + Code3 | Name1 |
| Code2 + Code3 | Name1 |
Code1 + Code2 | Name2 |
Code2 + Code3 | Name3 |
| Code2 + Code5 | Name3 |
| Code3 + Code6 | Name3 |
| Code5 + Code6 | Name3 |
KatkaS Try:
Table 2 = VAR __Table = GENERATE( 'TableCodes', DISTINCT(SELECTCOLUMNS(FILTER(ALL(TableCodes),[Name] = EARLIER(TableCodes[Name])),"__Code",[Code])) ) RETURN SELECTCOLUMNS( ADDCOLUMNS( FILTER(__Table,[Code] <> [__Code]), "__New",[Code] & " + " & [__Code] ), "Name",[Name], "Code",[__New] )
5 Replies
Greg_Deckler you are a genius, thank you!
KatkaS Try:
Table 2 = VAR __Table = GENERATE( 'TableCodes', DISTINCT(SELECTCOLUMNS(FILTER(ALL(TableCodes),[Name] = EARLIER(TableCodes[Name])),"__Code",[Code])) ) RETURN SELECTCOLUMNS( ADDCOLUMNS( FILTER(__Table,[Code] <> [__Code]), "__New",[Code] & " + " & [__Code] ), "Name",[Name], "Code",[__New] )Greg_Deckler , may I have an additional question...?
Your solution works as charm, but I received new file with added information - entity code (new column in the original table)
It means that one person could work for multiple companies and have various code combinations.
I would need to find out combinations (as you already showed me how), but within one company.
Could you look at that? Thank you very much!
KatkaS I imagine something along the lines of:
Table 2 = VAR __Company = "Company 1" VAR __Table = GENERATE( FILTER('TableCodes', [Company] = __Company DISTINCT(SELECTCOLUMNS(FILTER(ALL(TableCodes),[Name] = EARLIER(TableCodes[Name] && [Company] = __Company)),"__Code",[Code])) ) RETURN SELECTCOLUMNS( ADDCOLUMNS( FILTER(__Table,[Code] <> [__Code]), "__New",[Code] & " + " & [__Code] ), "Name",[Name], "Code",[__New] )
