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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
AOD
Helper II
Helper II

Keep last row from table comparing Field

Hi All, 

 

My table looks like as below , I would like to keep just last modified row in the table and delete all others . 

Thanks for helping in advance.

 

EmployeeValueModified date Prev modified date Previous value
EP123Yes22-Jun-20  
EP123No23-Jun-2022-Jun-20Yes
EP123Yes24-Jun-2023-Jun-20No
EP124Yes22-Jun-20  
EP124No23-Jun-2022-Jun-20Yes
EP125Yes21-Jun-20  
EP125No 22-Jun-2021-Jun-20Yes
EP125Yes23-Jun-2022-Jun-20No
EP125No24-Jun-2023-Jun-20Yes
2 ACCEPTED SOLUTIONS
parry2k
Super User
Super User

@AOD start a blank query, click advanced editor and paste the following code, you can apply the same logic in your table

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcg0wNDJW0lGKTC0GkkZGul6lebpGBkC2AhjH6iDU+OWDlBgjlCArBxmArBhqoAmSaiSdQKNgik2IsN2EFNtNEQYa4jLQFGygApopyOpxmIjDfiT/mMLdisPvYJNjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Employee = _t, Value = _t, #"Modified date " = _t, #"Prev modified date " = _t, #"Previous value" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee", type text}, {"Value", type text}, {"Modified date ", type date}, {"Prev modified date ", type date}, {"Previous value", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Employee"}, {{"Max Date", each List.Max([#"Modified date "]), type date}, {"All", each _, type table [Employee=text, Value=text, #"Modified date "=date, #"Prev modified date "=date, Previous value=text]}}),
    #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"Value", "Modified date ", "Prev modified date ", "Previous value"}, {"Value", "Modified date ", "Prev modified date ", "Previous value"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded All", each ([#"Modified date "] = [Max Date])),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Max Date"})
in
    #"Removed Columns"

 

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

ryan_mayu
Super User
Super User

@AOD 

 

You can also use DAX to create a table.

 

Table 2 = 
VAR tbl=ADDCOLUMNS('table (2)',"type",if('table (2)'[Modified date ]=MAXX(FILTER('table (2)','table (2)'[Employee]=EARLIER('table (2)'[Employee])),'table (2)'[Modified date ]),1,0))
return SUMMARIZE(FILTER(tbl,[type]=1),'table (2)'[Employee],'table (2)'[Value],'table (2)'[Modified date ].[Date],'table (2)'[Prev modified date ],'table (2)'[Previous value])

1.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

2 REPLIES 2
ryan_mayu
Super User
Super User

@AOD 

 

You can also use DAX to create a table.

 

Table 2 = 
VAR tbl=ADDCOLUMNS('table (2)',"type",if('table (2)'[Modified date ]=MAXX(FILTER('table (2)','table (2)'[Employee]=EARLIER('table (2)'[Employee])),'table (2)'[Modified date ]),1,0))
return SUMMARIZE(FILTER(tbl,[type]=1),'table (2)'[Employee],'table (2)'[Value],'table (2)'[Modified date ].[Date],'table (2)'[Prev modified date ],'table (2)'[Previous value])

1.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




parry2k
Super User
Super User

@AOD start a blank query, click advanced editor and paste the following code, you can apply the same logic in your table

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcg0wNDJW0lGKTC0GkkZGul6lebpGBkC2AhjH6iDU+OWDlBgjlCArBxmArBhqoAmSaiSdQKNgik2IsN2EFNtNEQYa4jLQFGygApopyOpxmIjDfiT/mMLdisPvYJNjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Employee = _t, Value = _t, #"Modified date " = _t, #"Prev modified date " = _t, #"Previous value" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee", type text}, {"Value", type text}, {"Modified date ", type date}, {"Prev modified date ", type date}, {"Previous value", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Employee"}, {{"Max Date", each List.Max([#"Modified date "]), type date}, {"All", each _, type table [Employee=text, Value=text, #"Modified date "=date, #"Prev modified date "=date, Previous value=text]}}),
    #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"Value", "Modified date ", "Prev modified date ", "Previous value"}, {"Value", "Modified date ", "Prev modified date ", "Previous value"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded All", each ([#"Modified date "] = [Max Date])),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Max Date"})
in
    #"Removed Columns"

 

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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