Selective row based on many scenarios

[erikmathijsen]

See my table above, which is just a subset of many rows.

What I'm trying to do is only listing the correct row based on SKU & Type for the related Privilege.

Privilege is important and determine which SKU is triggered. In case that we see a duplicate Privilege then we only want to return the SKU where Type = "Contracted"

If there are no duplicate privileges then we can return the single row

Expected outcome:

[_AAndrade · Accepted solution]

Hi @erikmathijsen,

In your example what was the criteria that you used to choose the first row of SKU B67293 instead of second row? 
Both rows have type = Unexpected?

[dufoq3]

Hi, different approach:

You can delete AddedIndex and SortedRows steps if sort order doesn't matter.

Result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjIzN7I0VtJRcgyID/cP8nYNig8I8gwDCoT6uUYEuDqHuLooxeqgKvR19HN0B6p0DHJ1xKXcwtDU0AC3cmd/v5AgR6zKQc7w8Xd0iY8AGuiOS4OloYGpOSF3xwIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SKU = _t, Privilege = _t, Type = _t]),
    AddedIndex = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    GroupedRows = Table.Group(AddedIndex, {"Privilege"}, {{"All", each 
        [ a = Table.SelectRows(_, (x)=> x[Type] = "CONTRACTED"),
          b = if Table.RowCount(a) > 0 then a else _
        ][b], type table}}),
    CombinedAll = Table.Combine(GroupedRows[All]),
    SortedRows = Table.Sort(CombinedAll,{{"Index", Order.Ascending}})
in
    SortedRows

[erikmathijsen]

Thank you very much !!

[erikmathijsen]

The criteria is that there is no "Contracted" SKU available, therefore the "Unexpected" SKU can be listed.

The privileges can be found against many SKUs, but I only have contracted only a few SKUs, which I've set to "Contracted"

In case a privilege is used, it should first try to find the Contracted SKU, if that is not available then it should list all Unexpected SKUs.

The combination SKU & TYpe = "Contracted" is unique.

[_AAndrade]

Sorry but I'm not getting your ideia.

According of your rules your output must show the two rows for SKU B67293 and not only one row.

The only thing I'm thinking is you are using role column to distinct instead of sku

[erikmathijsen]

Let me try to explain, therefore adding one more row..

Type = contracted means that we have purchased this SKU and each SKU has Privileges (1 or more).

And end user can be assigned to Privileges, which could trigger multiple SKUs.

If the Privilege is identified against a Contracted SKU, then all Unexpected SKUs can be ignored.

If the Privilege is identified against Unexpected SKU(s) then all Unexpected SKU(s) need to be listed.

Additional Explanation

AP_WORKER_PRIV is listed twice (B67293 & B91057), both of Type = Unexpected.  Because we don't have a SKU, where type is "Contracted" for this Privilege, both rows need to be displayed.

AP_MANAGER_AREA_PRIV is listed twice (B67293 <Type ="Unexpected"> & B81510 <Type = "Contracted".   In this case we are only interested in the "Contracted SKU.

Hope this explains the logic/rational behind my question.

[_AAndrade · Accepted solution]

Hi @erikmathijsen ,

Here is my solution:

1. I'm using this data set:

2. My M script code is this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCvYOVdJRCsrPSXXOT0kFMgOKMssycxLTQeyQyoJUpVidaCUnM3MjS2OgiGNAfLh/kLdrULyXvxMKPyDIMwwoEOrnGhHg6hzi6oKh0dfRz9EdWSdMwDHI1RGXfgtDU0MDEvQ7+/uFBDli1Q9yqI+/owuq08EiEUA73XEZYWloYGpOqt9jAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    PromotedHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    ChangedType = Table.TransformColumnTypes(PromotedHeaders,{{"SKU", type text}, {"RoleCode", type text}, {"Privilage", type text}, {"Type", type text}}),
    GroupTable = Table.Group(ChangedType, {"Privilage"}, {
        {"GroupTable", each if List.ContainsAny([Type], {"CONTRACTED"}) then Table.SelectRows(_, each [Type] = "CONTRACTED") else _}
    }),
    ExpandTable = Table.ExpandTableColumn(GroupTable, "GroupTable", {"SKU", "RoleCode", "Privilage", "Type"}, {"SKU", "RoleCode", "Privilage.1", "Type"}),
    RemoveColumns = Table.RemoveColumns(ExpandTable,{"Privilage"}),
    RenameColumn = Table.RenameColumns(RemoveColumns,{{"Privilage.1", "Privilage"}}),
    Result = Table.TransformColumnTypes(RenameColumn,{{"Privilage", type text}, {"SKU", type text}, {"RoleCode", type text}, {"Type", type text}})
in
    Result

3. Final Output:

[_AAndrade · Accepted solution]

Hi @erikmathijsen,

In your example what was the criteria that you used to choose the first row of SKU B67293 instead of second row? 
Both rows have type = Unexpected?