Variation of generating a list of combinations within Power Query

Mo.jo.jo · October 18, 2020, 9:09am

Hi all

I am attempting to implement row level security (to determine which users can access specific rows within a Power BI report) and I am required to create a sort of bridge table that contains the required combinations of User IDs and Group IDs. I have not had any joy achieving this within Power Query. Again

The general logic is that all region managers (IDs highlighted yellow) should be able to access their own group as well as other groups within their region) but with a twist; group managers should be able to access only theirs and other group manager rows but not the regional manager group information). Sigh.

Hopefully makes more sense on viewing that attached images.

Current state:

Desired outcome: Desired state

It really would be great if this could be done dynamically within Power Query as that seems more efficient and also because it might be even more difficult for me to produce the DAX code to achieve this.
Comments and suggestions as always much appreciated.

Thank you.

Bridge Table.xlsx (15.1 KB) Creating a bridge table.pbix (17.5 KB)

MK3010 · October 18, 2020, 11:05am

Hi @Mo.jo.jo,

Best you can use the DAX but as you requested PQ. You can use below code to get desired result.

let
    Source = Excel.CurrentWorkbook(){[Name="Table134"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Region ID", Int64.Type}, {"Region Name", type text}, {"Region Manager ID", Int64.Type}, {"Group ID", Int64.Type}, {"Group Manager ID", Int64.Type}, {"Group Type", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Group Type] = 2)),
    #"Merged Queries" = Table.NestedJoin(#"Filtered Rows", {"Group Type", "Region ID"}, Source, {"Group Type", "Region ID"}, "Source", JoinKind.LeftOuter),
    Gr2Expand = Table.ExpandTableColumn(#"Merged Queries", "Source", {"Group ID"}, {"Access to Group"}),
    #"Removed Other Columns" = Table.SelectColumns(Gr2Expand,{"Group Manager ID", "Access to Group"}),
    Gr2 = Table.RenameColumns(#"Removed Other Columns",{{"Group Manager ID", "User ID"}}),
    Custom1 = Table.SelectColumns(#"Changed Type",{"Region Manager ID", "Group ID"}),
    Gr1 = Table.RenameColumns(Custom1,{{"Region Manager ID", "User ID"}, {"Group ID", "Access to Group"}}),
    Custom2 = Table.Combine( {Gr1, Gr2} )
in
    Custom2

Below is screen shot FYR.

Melissa · October 18, 2020, 11:33am

Hi @Mo.jo.jo,

Seems @MK3010 beat me to it but since my solution is different thought I would share it any way…

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwUNJRCkpNz8zPUzAEMs1NgIQhCMA5SrE6+BQaAWkjkIgRAYXGQNrUgAiFINrEAq7QCFkhyDYzc7CVEDeCORA3GiMrBNlmDuIbA9WZgWi4gbjVmcM4BAw0AtFmUL/EAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Region ID" = _t, #"Region Name" = _t, #"Region Manager ID" = _t, #"Group ID" = _t, #"Group Manager ID" = _t, #"Group Type" = _t]),
    #"Added Custom" = Table.AddColumn(Source, "IsRegionManager", each List.Contains( List.Distinct( Source[Region Manager ID] ), [Group Manager ID] ), type logical),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Region Name"}, {{"AllRows", each _, type table [Region ID=nullable text, Region Name=nullable text, Region Manager ID=nullable text, Group ID=nullable text, Group Manager ID=nullable text, Group Type=nullable text, IsRegionManager=logical]}}),
    TransformNestedTables = Table.AddColumn(#"Grouped Rows", "Custom", each 
        let
            myTable = [AllRows],
            AddListGroups = Table.AddColumn(myTable, "List Groups", each if [IsRegionManager] = true then myTable[Group ID] else Table.SelectRows(myTable, (BT)=> BT[IsRegionManager] = false )[Group ID], type list),
            ExpandListGroups = Table.ExpandListColumn(AddListGroups, "List Groups"),
            ChangeType = Table.TransformColumnTypes(ExpandListGroups,{{"List Groups", type text}})
        in 
            ChangeType, type table )[[Custom]],
    ExpandNestedTables = Table.ExpandTableColumn(TransformNestedTables, "Custom", {"Region ID", "Region Name", "Region Manager ID", "Group ID", "Group Manager ID", "Group Type", "IsRegionManager", "List Groups"}, {"Region ID", "Region Name", "Region Manager ID", "Group ID", "Group Manager ID", "Group Type", "IsRegionManager", "List Groups"})[[Group Manager ID], [List Groups]],
    ChangeType = Table.TransformColumnTypes(ExpandNestedTables,{{"Group Manager ID", type text}, {"List Groups", type text}}),
    RenameColumns = Table.RenameColumns(ChangeType,{{"Group Manager ID", "User ID"}, {"List Groups", "Access to Groups"}})
in
    RenameColumns

.

I added a column to identify Group Managers

Added a Group By step on Region Name

Next I created a list with Group ID’s based on a condition for each nested table

Table.AddColumn(myTable, "List Groups", each 
    if [IsRegionManager] = true then myTable[Group ID] else 
    Table.SelectRows(myTable, (BT)=> BT[IsRegionManager] = false )[Group ID], type list)

Expanded that table and did some clean up with this result.

Here’s your sample file. Creating a bridge table.pbix (18.0 KB)
I hope this is helpful.

Mo.jo.jo · October 18, 2020, 12:17pm

@MK3010 Thank you very much for putting that solution together. Simple yet potent. It seems all the ingredients were in place but I am a terrible Power Query chef; all the ingredients looked unusable
If you can spare the time please feel free to share how you might do this using DAX. Again, was not sure how to start with that. And it would be useful learning.

@Melissa
Very helpful indeed.
Another ingenious approach adding to the tips you used to solved my previous issue.
I have read through your solutions with equal parts cold-sweat (my lack of current understanding) and inspiration at how much one can do with Power Query.ha ha.
Thank you.

BrianJ · October 18, 2020, 6:03pm

@Mo.jo.jo,

Writing long, complex DAX in situations where Power Query would be far more appropriate is sort of my signature move, so I couldn’t resist the challenge on that.

I’m away from my computer, but as soon as I get back I will post it. Definitely the wrong tool for the job, but fun to use nonetheless…

– Brian

BrianJ · October 18, 2020, 10:21pm

@Mo.jo.jo,

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

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

Full “solution” file posted below.

Brian
eDNA Forum - Creating a bridge table DAX Solution2.pbix (27.1 KB)

Mo.jo.jo · October 19, 2020, 9:05am

@BrianJ
The bubbling love hate relationship you have with writing an unnecessarily complex piece of coding came across quite nicely
Glad I could offer up the temptation.
I will go through the code, seeing as reviewing such code is also an increasingly active hobby. And either which way I learn.

You have previously mentioned how verbose the DAX approach often is compared to sorting the matter out in PQ, and this is the proof pudding, and your caveat comes stamped all over the “solution”.

Much obliged.