Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
tom1tas
Frequent Visitor

Obtaining unique values based on a given condition

Hello Everyone,
I currently have a query that contains data from hours reported towards different companies in 2022-2023 organized on a daily date format.  Therefore for each day, I have a split of hours and sometimes one employee can work for several different companies during the same day. What I need is to obtain a new table where I only keep the company where the employee reported the most of its time to as shown in the tables. 

Current table

Employee IDCompanyWorking HoursDate

123

BananasCompany72/14/2022
123ComputersCompany12/14/2022
123Computers Company62/15/2022
123Bananas Company12/15/2022
123water Company12/15/2022

 

Expected table

 

Employee IDCompanyWorking HoursDate

123

BananasCompany72/14/2022
123Computers Company62/15/2022


Notice It happens to multiple employees at once in the real data base therefore I will hold several employee id  associated to each date but each employee ID can be found on each date only once, by the condition of keeping the value that has the most of the time reported for that day.


Appreciate your help

8 REPLIES 8
wdx223_Daniel
Super User
Super User

NewStep=Table.FromRecords(Table.Group(PreviousStepName,"Employee",{"n",each Table.Max(_,"Working Hours")})[n])

  I need it to keep all dates historically for each employee id, the only condition is that I need to keep one company per each date based on the maximum number of hours reported. 

This m code script keeps only maximum from latest date available, is there a way to keep all the dates available and in case the employee worked for two companies the same day the same amount of time, to condition it to choose  whichever company that is not "milk company"

 

Employee idcompanyhoursdate
123Milk Company31/1/2023
123Cars Company31/1/2023

Expected table

 

Employee idcompanyhoursdate
123Cars Company31/1/2023

 

Scenario where hours amount is different from each company

 

Existing table

Employee idcompanyhoursdate
123Goats Company41/1/2023
123Cars Company31/1/2023

 

Expected table

 

Employee idcompanyhoursdate
123Goats Company41/1/2023
    
    
    
adudani
Super User
Super User

hi @tom1tas ,

 

create a blank query and in the advanced editor , copy paste the following code:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRckrMA8Ji5/zcgsS8SqCAORAb6Rua6BsZGBkpxerAFIJUlJakFiEpNSSkVAGh1gyi1hRdLdR+BQxTMVSWJwKNxK0uFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Employee ID" = _t, Company = _t, #"Working Hours" = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee ID", Int64.Type}, {"Company", type text}, {"Working Hours", Int64.Type}, {"Date", type date}}),
    #"Grouped Rows by Employee and Date" = Table.Group(#"Changed Type", {"Employee ID", "Date"}, {{"Details", each _, type table [Employee ID=nullable number, Company=nullable text, Working Hours=nullable number, Date=nullable date]}}),
    #"Added Index Column in the Details Column" = Table.AddColumn(#"Grouped Rows by Employee and Date", "Custom", each Table.AddIndexColumn([Details],"Index")),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Index Column in the Details Column", "Custom", {"Company", "Index"}, {"Company", "Index"}),
    #"Take Value with Most hours worked" = Table.SelectRows(#"Expanded Custom", each ([Index] = 0)),
    #"Removed Columns" = Table.RemoveColumns(#"Take Value with Most hours worked",{"Details", "Index"})
in
    #"Removed Columns"

 

Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a Kudos 🙂
Kind Regards,
Avinash

Can you explain it as query steps? I used dummy data therefore it does not work for me to get m code solutions

,

@tomsuare @tom1tas ,

So first two steps don't really matter ( source and change type)

After that:

1. Grouped rows by employee id and date aggregating "details" as all the rows which will give a table with all rows for that employee id and date.

2. Added an index column called "Custom" in this "Details" table 

3. Expand Company, hours worked and index column 

4. Filter index for 0. ( First row contains the record with MAX working hours) .

 

Hope this helps.

 

 

Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a Kudos 🙂
Kind Regards,
Avinash

tom1tas_0-1676555161324.png

Hey sincerely thanks for your response, I am at the point where you group by employee id and dates and include all rows with no summarization, I am not sure when to index as I tried doing it after and before expanding and it does not keep my max company hours as you stated,

let me know how to proceed from here,

thanks kindly

 

 

@tom1tas 

It's after the step of grouping.

Create a custom column:

 

Table.AddIndexColumn([Details],"Index")

 

This creates a new column with all rows in the table present in the details column, as well as the added index from 0.

Let me know if you have other questions.

 

@wdx223_Daniel  has a pretty elegant solution as well if you'd like to implement.

Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a Kudos 🙂
Kind Regards,
Avinash

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.