topic Group by in DAX Commands and Tips

Group by

jimpatel — Wed, 23 Aug 2023 09:53:50 GMT

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

Re: Group by

rrm121812 — Wed, 23 Aug 2023 10:36:18 GMT

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. 😊

Re: Group by

jimpatel — Wed, 23 Aug 2023 13:57:45 GMT

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

Re: Group by

rrm121812 — Thu, 24 Aug 2023 05:10:06 GMT

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.

Re: Group by

jimpatel — Thu, 24 Aug 2023 10:10:51 GMT

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

Re: Group by

rrm121812 — Thu, 24 Aug 2023 10:42:41 GMT

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.

Re: Group by

jimpatel — Thu, 24 Aug 2023 10:55:10 GMT

Thanks a lot 🙂