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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
frenchy988
Frequent Visitor

Keep data with latest approval date

frenchy988_0-1737497209030.png

I am trying to analyse bunch of budgets for clients that gets updated as needed but ultimalty keep a table would host only the latest budget information, I thought of doing it this way which is indeed keeping the latest day approved but then the other data gets messed up.. any advise 

let
Source = #"Budgets ALL",
#"Grouped Rows" = Table.Group(Source, {"Medicaid ID Number:"}, {{"LatestRow", each Table.Max(_, "Transmittal Date:"), type record}}),
#"Expanded LatestRow" = Table.ExpandRecordColumn(#"Grouped Rows", "LatestRow", {"Name", "Individual Name", "Annual Budget Effective Date:", "Transmittal Date:", ..})
in
#"Expanded LatestRow"

 

1 ACCEPTED SOLUTION
Akash_Varuna
Super User
Super User

Hi I think try these steps 

  • Sort the table by "Transmittal Date:" in descending order to bring the latest records to the top.
  • Remove duplicates based on "Medicaid ID Number:" to keep only the most recent entry for each ID.
  • Expand necessary columns to include additional data you need for analysis.
  • Load the cleaned table back into your report or worksheet.


    Try this PowerQuery : 
    let
    Source = #"Budgets ALL",
    #"Sorted Rows" = Table.Sort(Source, {"Transmittal Date:", Order.Descending}),
    #"Removed Duplicates" = Table.Distinct(#"Sorted Rows", {"Medicaid ID Number:"})
    in
    #"Removed Duplicates"
    Try this solution and if this helps please accept this as solution and give a kudos

 

 

View solution in original post

6 REPLIES 6
v-karpurapud
Community Support
Community Support

Hi @frenchy988 

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.


v-karpurapud
Community Support
Community Support

Hi @frenchy988

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.

v-karpurapud
Community Support
Community Support

Hi @frenchy988 

 

I wanted to follow up and ask if you have tried the steps provided by @Akash_Varuna . Could you please confirm whether your query has been resolved? If not, please share detailed information about the issue you are facing so that we can better understand it and provide you with a possible solution. If your issue has been resolved, kindly mark the helpful response and accept it as the solution. This will assist other community members in resolving similar issues more efficiently.

Thank you!

dufoq3
Super User
Super User

Hi @frenchy988, what do you mean with "but then the other data gets messed up"?


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Akash_Varuna
Super User
Super User

Hi I think try these steps 

  • Sort the table by "Transmittal Date:" in descending order to bring the latest records to the top.
  • Remove duplicates based on "Medicaid ID Number:" to keep only the most recent entry for each ID.
  • Expand necessary columns to include additional data you need for analysis.
  • Load the cleaned table back into your report or worksheet.


    Try this PowerQuery : 
    let
    Source = #"Budgets ALL",
    #"Sorted Rows" = Table.Sort(Source, {"Transmittal Date:", Order.Descending}),
    #"Removed Duplicates" = Table.Distinct(#"Sorted Rows", {"Medicaid ID Number:"})
    in
    #"Removed Duplicates"
    Try this solution and if this helps please accept this as solution and give a kudos

 

 

This may not work as expected without buffering.


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Top Kudoed Authors