Group by

[jimpatel]

Hi, 

Thanks for looking at my post. 

I am trying to link 2 tables and i wanted to get unique number rather than repeating numbers to connect two table. 

What i am looking for is in "Transform data" tab i wanted to find the latest date for particular number and show the comments for those numbers and ignore the repeation. 

Example is below. Any idea about this please?

Original data :

Result looking for:

Thanks a lot 

[rrm121812 · Accepted solution]

HI,

You can follow the below steps to achieve the result:

1. Go to Transform Data-->Group By in Power Query Editor

2. Fill the Group By pop up window like this: 

You will see the result as expected.

Before:

After Group By:

Regards,

Ritu

Please Accept the answer as solution if this solves your problem. Happy to help. 😊

[jimpatel]

Thanks a lot 🙂

[jimpatel]

Thanks a lot for your time and effort. One last thing in your logic is it is taking max date. That is if two dates, one is 30/02/2020 and other one is 20/03/2023, it is considering first one as the max date is 30th. Is there anyway to find solution for this pleasE?

thanks a lot and thanks a ton for your help again

[rrm121812 · Accepted solution]

It is because Date column datatype is set to String. If you convert it into Date, it will resolve the issue.

If you find this insightful, please give Kudos and Accept it as a solution.

[jimpatel]

Thanks a lot for your reply. I have tried this before but unfortunately its not picking the latest date comments. 

For comments its picking up the last alphabet starting letter. That is if i have multiple comments, its picking the letter starting in the very last alphabets.

Any idea please?

Thanks

[rrm121812]

Hi,

To simulate your case I have added a column Comments to the data table with different alphabets. There is a way you can still Group By Number.

1. Create a custom column by merging Date and Comments with the use of any delimiter. I have use "-".

2. Then Groupby Number and aggregate over the new custom column (Using MAX)

3. After groupby split the column by delimiter. And you will get the comments corresponding to the max date.

Group By.pbix 

Hope this helps.

[rrm121812 · Accepted solution]

HI,

You can follow the below steps to achieve the result:

1. Go to Transform Data-->Group By in Power Query Editor

2. Fill the Group By pop up window like this: 

You will see the result as expected.

Before:

After Group By:

Regards,

Ritu

Please Accept the answer as solution if this solves your problem. Happy to help. 😊