Forum Discussion

2019's avatar
2019
Helper II
5 years ago
Solved

Extract Recent Date With It's Corresponded Row

I have tried to search in google and in this community to find a solution for this, unfortunately no luck.

I have two tables as per the attachements in links below:

 

Data Source Excelsheet File 

Power Bi File 

 

I would like to show only the latest date with its corresponded row for each well number.

Need Help
  • Fowmy's avatar
    Fowmy
    5 years ago

    2019 

     

    After Grouping as you showed, expand the "Rest of Column".

     

    add a custom column where you type 

    [Rig Spud] = [Max Date]

    now filter only True from this column then delete unnecessary columns 

     

     

     

6 Replies

  • Jakinta's avatar
    Jakinta
    Solution Sage
    5 years ago

    You can Sort Descending [Rig Spud], then Keep Top Rows, only first.

  • Fowmy's avatar
    Fowmy
    Super User
    5 years ago

    2019 

    Click on the filter of the "Rig Spud" column and choose "Is Latest" under the date filter as shown in the example below.

     

  • 2019's avatar
    2019
    Helper II
    5 years ago

    Fowmy 

    Jakinta 

    Thank you for the answers, unfortunately it does not work with my situation, i have edited the post and added the data source file alongside with the power bi native file for more details.

    • 2019's avatar
      2019
      Helper II
      5 years ago

      Fowmy 

      Yes of course,

       

      The desired result would be a table with the latest dates of Rig spud for each well number.

       

      Let’s take Ru-160 well number as example, in SPUD table/query as you can see there are 4 rows for this well, the desired result would be one row which contain the latest Rig spud date, in this example it’s the 3rd row which have the date 10/27/2020 as shown in below image, and so on for the rest of the well numbers in tables.

       

      I was trying to use the group by with Max () function operation but it did not fully work, it was pulling the latest date but the wrong Rig number, that is why I was hoping to find a way where well number get checked row by row and find the latest Rig spud date, then power bi pull that particular row alongside with  corresponded rows (Rig number and station)

      • Fowmy's avatar
        Fowmy
        Super User
        5 years ago

        2019 

         

        After Grouping as you showed, expand the "Rest of Column".

         

        add a custom column where you type 

        [Rig Spud] = [Max Date]

        now filter only True from this column then delete unnecessary columns