Variation of generating a list of combinations within Power Query

Power Query
6

@Mo.jo.jo,

A totally inappropriate and irresponsible use of DAX, posted for educational and entertainment purposes only:

image

Rearrange = 

VAR Gp1Tbl =
CALCULATETABLE(
    SUMMARIZE(
        'Data Table',
        'Data Table'[Region Manager ID],
        'Data Table'[Group Manager ID],
        'Data Table'[Group ID]
    ),
    'Data Table'[Group Type] = 1
)

VAR TopLevel =
CALCULATETABLE(
    VALUES( 'Data Table'[Group Manager ID] ),
    FILTER(
        'Data Table',
        'Data Table'[Group Manager ID] = 'Data Table'[Region Manager ID]
    )
)

VAR RegIDTabl =
SUMMARIZE(
    'Data Table',
    'Data Table'[Group Manager ID],
    'Data Table'[Region ID]
)

VAR CrossJnTab1 =
    CROSSJOIN(
        SUMMARIZE( 'Data Table', 'Data Table'[Group Manager ID] ),
        SUMMARIZE( 'Data Table', 'Data Table'[Group ID] )
    )
    
VAR RegIDGet1 =
    NATURALLEFTOUTERJOIN(
        CrossJnTab1,
        RegIDTabl
    )

VAR FiltRegIDGet1 =
FILTER(
    RegIDGet1,
    [Group Manager ID] IN TopLevel &&
    LEFT( 'Data Table'[Region ID], 1 ) = LEFT( 'Data Table'[Group ID], 1 )
)

VAR Gp1Tb2 =
CALCULATETABLE(
    SUMMARIZE(
        'Data Table',
        'Data Table'[Group Manager ID],
        'Data Table'[Group ID]
    ),
    'Data Table'[Group Type] = 2
)

VAR CrossJnTab2 =
    CROSSJOIN(
        SUMMARIZE( Gp1Tb2, 'Data Table'[Group Manager ID] ),
        SUMMARIZE( Gp1Tb2, 'Data Table'[Group ID] )
    )

VAR RegIDGet2 =
    NATURALLEFTOUTERJOIN(
        CrossJnTab2,
        RegIDTabl
    )

VAR FiltRegIDGet2 =
FILTER(
    RegIDGet2,
    LEFT( 'Data Table'[Region ID], 1 ) = LEFT( 'Data Table'[Group ID], 1 )
)

VAR Result =
UNION(
    SUMMARIZE( FiltRegIDGet1, 'Data Table'[Group Manager ID], 'Data Table'[Group ID] ),
    SUMMARIZE( FiltRegIDGet2, 'Data Table'[Group Manager ID], 'Data Table'[Group ID] )
)

vAR SummResult =
SUMMARIZE( 
    Result,
    'Data Table'[Group Manager ID],
    'Data Table'[Group ID]
)

RETURN 
SummResult

image

Full “solution” file posted below.

4 Likes