Forum Discussion

erikmathijsen's avatar
erikmathijsen
New Member
2 years ago
Solved

Selective row based on many scenarios

  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 det...
Need Help
  • _AAndrade's avatar
    _AAndrade
    2 years ago

    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?

     

  • _AAndrade's avatar
    _AAndrade
    2 years ago

    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:

     

     

erikmathijsen's avatar
erikmathijsen
New Member
2 years ago

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's avatar
    _AAndrade
    Resident Rockstar
    2 years ago

    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's avatar
      erikmathijsen
      New Member
      2 years ago

      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's avatar
        _AAndrade
        Resident Rockstar
        2 years ago

        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: