Help with a query

Hi 83dons,

Thank you for reaching out to the Microsoft fabric community forum. Thank you Ashish_Excel bhanu_gautam, for your inputs on this issue

After thoroughly reviewing the details you provided, I was able to reproduce the scenario, and it worked on my end. I have used it as sample data on my end and successfully implemented it.    

Go to get data> blank query. Then open advance editor then paste below M Code:

M Code:

let

    // Sample input table

    Source = Table.FromRows({

        {"0001", "A001", "EMPLOYED", 91},

        {"0001", "A002", "EMPLOYED", 25},

        {"0001", "A003", "EMPLOYED", 0},

        {"0002", "A004", "EMPLOYED", 50},

        {"0002", "A005", "LEFT", 100},

        {"0003", "A006", "LEFT", 20},

        {"0003", "A007", "LEFT", 20}

    }, {"ClientID", "RoleID", "Status", "WTE"}),



    // Add numeric ranking for Status to help sort (EMPLOYED = 1, LEFT = 2)

    AddStatusRank = Table.AddColumn(Source, "StatusRank", each if [Status] = "EMPLOYED" then 1 else 2, Int64.Type),



    // Sort by ClientID, StatusRank, then WTE descending

    SortedTable = Table.Sort(AddStatusRank, {

        {"ClientID", Order.Ascending},

        {"StatusRank", Order.Ascending},

        {"WTE", Order.Descending}

    }),



    // Group by ClientID, keep all rows in nested tables

    GroupedRows = Table.Group(SortedTable, {"ClientID"}, {"AllRoles", each _, type table}),



    // Add an index column inside each nested table (starting at 1)

    AddIndexInGroup = Table.AddColumn(GroupedRows, "WithIndex", each Table.AddIndexColumn([AllRoles], "RoleIndex", 1, 1, Int64.Type)),



    // Remove old grouped column and expand WithIndex

    RemoveOldGroup = Table.RemoveColumns(AddIndexInGroup, {"AllRoles"}),



    ExpandedTable = Table.ExpandTableColumn(RemoveOldGroup, "WithIndex", {"RoleID", "Status", "WTE", "StatusRank", "RoleIndex"}),



    // Add Default Role column based on RoleIndex = 1

    AddDefaultRole = Table.AddColumn(ExpandedTable, "Default Role", each if [RoleIndex] = 1 then true else false, type logical),



    // Remove helper column StatusRank and RoleIndex

    RemoveHelperCols = Table.RemoveColumns(AddDefaultRole, {"StatusRank", "RoleIndex"})

in

    RemoveHelperCols

outcome:

I am also including .pbix file for your better understanding, please have a look into it:

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.