Forum Discussion

jimpatel's avatar
jimpatel
Icon for Post Patron rankPost Patron
3 years ago
Solved

Group by

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 d...
  • rrm121812's avatar
    rrm121812
    3 years ago

    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. ğŸ˜Š

     

  • rrm121812's avatar
    rrm121812
    3 years ago

    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's avatar
jimpatel
Icon for Post Patron rankPost Patron
3 years ago

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's avatar
    rrm121812
    Icon for Helper II rankHelper II
    3 years ago

    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.