Help with a query

[83dons]

Hi I have a list of employee roles. An employee may have several roles and some may have end dates (be old). I need to set the value in a column to True or False based on various criteria for every role. If true then this is their default role and if false it is not their default role. Each employee can only have one default role (true) and all other roles would then need to be set to false. Sample data:

ClientID	RoleID	Status	WTE (out of 100)	Default Role
0001	A001	EMPLOYED	91	True
0001	A002	EMPLOYED	25	False
0001	A003	EMPLOYED	0	False
0002	A004	EMPLOYED	50	True
0002	A005	LEFT	100	False
0003	A006	LEFT	20	True
0003	A007	LEFT	20	False

As you can see its groupd by CLientID then RoleID then Status then WTE. The query needs to then populate the Default Role column with True for the top entry per ClientID group of roles and False for all other rows of roles for that client. Each client can only have one True default role and all the rest must be False.

Any ideas how I can achive this using Power BI Desktop transform? I used to be able to do it using an Excel Makro but I would like to use Power BI now. It is something to do with sorting the table into groups based on the above criteria and then marking the top row of each group True in the the new column. Possibly to default all to False prior to doing this might work when initially creating the new column I guess.

[v-kpoloju-msft · Accepted solution]

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.

[v-kpoloju-msft]

Hi @83dons,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[83dons]

Hi I will be trying one of these later today and will mark which one that works best.

[v-kpoloju-msft]

Hi @83dons,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[Ashish_Excel]

Hi,

This M code in Power Query works

let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"ClientID"}, {{"Count", each Table.AddIndexColumn(_,"Index")}}),
#"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"RoleID", "Status", "WTE (out of 100)", "Index"}, {"RoleID", "Status", "WTE (out of 100)", "Index"}),
#"Added Custom" = Table.AddColumn(#"Expanded Count", "Custom", each if [Index]=0 then true else false)
in
#"Added Custom"

Hope this helps.

[bhanu_gautam]

@83dons 

Go to Home > Transform Data to open the Power Query Editor.
Sort the data:

Sort your data by ClientID, Status (to prioritize 'EMPLOYED' over 'LEFT'), and WTE in descending order. This ensures that the top role per ClientID is the one with the highest WTE and is EMPLOYED.
Add an Index Column:

Go to Add Column > Index Column > From 0. This will help in identifying the top row for each ClientID.
Group by ClientID:

Go to Home > Group By.
Group by ClientID.
Add an aggregation for All Rows to keep all the data in each group.
Add a Custom Column to set Default Role:

Add a custom column to set the "Default Role" based on the index. The first row (index 0) in each group will be set to True, and the rest will be False.

[83dons]

Hi @bhanu_gautam I got lost at about the 'Add a Custom Column to set Default Role' stage. There also arent any instrcutions on how to return the table to its original format with just the Default column populated with True or False, removing all the groupings etc. Can you provide more detailed advice?

[83dons]

Thanks very much for the quick reply. I will try this today and let you know how it goes.