Forum Discussion

tom1tas's avatar
tom1tas
Frequent Visitor
3 years ago

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 ...
General Comment
Need Help
Tips and Tricks
Tutorial Requests
Anonymous's avatar
Anonymous
Not applicable
3 years ago

,

adudani's avatar
adudani
Memorable Member
3 years ago

Anonymous 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.

 

 

  • tom1tas's avatar
    tom1tas
    Frequent Visitor
    3 years ago

    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

     

     

    • adudani's avatar
      adudani
      Memorable Member
      3 years ago

      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.