Forum Discussion
Help with a query
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.
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.
8 Replies
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.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.
Hi I will be trying one of these later today and will mark which one that works best.
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.
Thanks very much for the quick reply. I will try this today and let you know how it goes.
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?
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.
