cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nckpedersen
Helper I
Helper I

Fields with the same value are not aggregating in table

I have a calculated column with a simple lookupvalue formula which appends an organization name to a unique id from another table.

When I put the new column in a table, all of the duplicate values have their own row. Using the original column, those values are aggregated. Even worse, some are actually aggregating... I've copied the values into excel and run a simple deduplication and the values all disappear so I know there are no spelling changes, added spaces, etc.

I cannot seem to find any reason for this. Suggestions? 


nckpedersen_0-1664574109241.png

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@nckpedersen , Please use clean and Trim in Power Query.

If there is a sort column for the organization, make sure that have only one value for one value of the organization.

 

Also, make sure that the count of Organization is a count or sum

 

Power Query Trim and Clean : https://youtu.be/NRYPsCnS0w4

How to Create Sort Column and Solve Related Errors:
https://www.youtube.com/watch?v=KK1zu4MBb-c

View solution in original post

3 REPLIES 3
Ashish_Mathur
Super User
Super User

Hi,

Share the download link of the PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
amitchandak
Super User
Super User

@nckpedersen , Please use clean and Trim in Power Query.

If there is a sort column for the organization, make sure that have only one value for one value of the organization.

 

Also, make sure that the count of Organization is a count or sum

 

Power Query Trim and Clean : https://youtu.be/NRYPsCnS0w4

How to Create Sort Column and Solve Related Errors:
https://www.youtube.com/watch?v=KK1zu4MBb-c

Thank you! It was indeed because I set a sort column. Interesting effect, might use later now that I know how it works! I really appreciate the help! 

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power BI Dev Camp Session 27

Ted's Dev Camp

This session walks through creating a new Azure AD B2C tenant and configuring it with user flows and custom policies.