Forum Discussion

KatkaS's avatar
KatkaS
Post Patron
3 years ago
Solved

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 ...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    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's avatar
Greg_Deckler
Community Champion
3 years ago

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]
    )
  • KatkaS's avatar
    KatkaS
    Post Patron
    3 years ago

    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!

    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      3 years ago

      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]
    )
      • KatkaS's avatar
        KatkaS
        Post Patron
        3 years ago

        Greg_Deckler I appreaciate you support very much! I tried above solution, but it returnes an syntax error.. could you please loot at it? I will try to send you the dashboard via personal message. Thank you!