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:

     

     

dufoq3's avatar
dufoq3
Icon for Community Champion rankCommunity Champion
2 years ago

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