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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Kish1999
Helper II
Helper II

Grouping and removing rows in Power Query Editor

Hello All,

 

I have a scenaio where I have list of projects and their various updates on certain days. I want to keep just the rows with the latest updates. I have to accomplish this in Power Query editor because i will be using this dataset for a Paginated report. Below is the sample input and output: (I can do this by creating measures and writing DAX, but i have to do this in Power query)

 

ProjectUpdate dateComment
   
A05-04-2023asd
A07-04-2023zxc
A10-04-2023qwe
B03-03-2023cvb
B09-04-2023dgg
C08-04-2023rfre
   
Output  
ProjectUpdate DateComment
A10-04-2023qwe
B03-03-2023cvb
C08-04-2023rfre
1 REPLY 1
DOLEARY85
Super User
Super User

Hi,

 

This is how i've done it:

 

  1. Duplicate the table in power query
  2. in the duplicate table use group by on Project with aggregation on Max of Update Date
  3. Merge Queries with the original table and join on Project and Date 
  4. Expand the new column and select only comment to bring through the relevant comment

DOLEARY85_0-1681210022803.png

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors