cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Problem with GROUP BY for Duplicates

The source table has 3 columns:

[User]
[Delegate]
[Permissions]

 

The three columns combined should create a compound natural key, but I have found some duplicates using T-SQL;

 

SELECT
  [User],
  [Delegate],
  [Permissions] 
FROM [dbo].[tblImport]
WHERE [Delegate] <> 'None'
GROUP BY
  [User],
  [Delegate],
  [Permissions]
HAVING COUNT(*) > 1;

 

I tried to re-create this using Power BI.  I went into Power Query mode, selected the three columns, clicked Transform|Group By, and created a new column called DataCount that uses the "Count Rows" Operation. 

 

Power BI Screen Shot.JPG

 

However, the new column has the value "1" for every row, even the ones that should be duplicates with a count > 1.  What am I doing wrong?

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III
Super User III

Re: Problem with GROUP BY for Duplicates

 Hi @imani_tech,

 

Made a small CSV file with the same fields as you have:

UserDelegatePermissions

AAAAAA
AAABBB
AAABBB
BBBAAA
CCCAAA
CCCBBB
CCCCCC
CCCCCC

 

As you can see I have repeated items, then made the grouping in Query editor and the result gives the duplicate in the count rows:

 

GRoup.png

 

Are you already getting the information from the CSV or from the SQL that you had in your previous post?

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





View solution in original post

6 REPLIES 6
Highlighted
Super User III
Super User III

Re: Problem with GROUP BY for Duplicates

Hi @imani_tech,

 

To what I can see from your SQL when you do a group by the 3 fields in your table and choose the ones that have a double count you are returning a single value for row and not the duplicated values.

 

To replicate this though in Power BI and return the ones that are above 1  count you need to take the group by part of your SQL and then use the Group by in Power query to return 1,2, 3... in the DataCount column.

 

Don't know if this is just a test of the capabilities of PBI but if your data source is already refined no need to go back and ungroup and then group it on PBI, let the SQL do that for you.

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





Highlighted
Frequent Visitor

Re: Problem with GROUP BY for Duplicates

Thanks for the response.  I'm actually supposed to complete this project within Power BI, since the data source is a .csv file.  However, I imported the .csv into SQL Server just to confirm that I'm doing the right thing.  

Highlighted
Super User III
Super User III

Re: Problem with GROUP BY for Duplicates

If you upload the CSV file to PBI and do what you were doing you should get the correct result.

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





Highlighted
Frequent Visitor

Re: Problem with GROUP BY for Duplicates

It should work but it isn't.

Highlighted
Super User III
Super User III

Re: Problem with GROUP BY for Duplicates

 Hi @imani_tech,

 

Made a small CSV file with the same fields as you have:

UserDelegatePermissions

AAAAAA
AAABBB
AAABBB
BBBAAA
CCCAAA
CCCBBB
CCCCCC
CCCCCC

 

As you can see I have repeated items, then made the grouping in Query editor and the result gives the duplicate in the count rows:

 

GRoup.png

 

Are you already getting the information from the CSV or from the SQL that you had in your previous post?

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





View solution in original post

Highlighted
Frequent Visitor

Re: Problem with GROUP BY for Duplicates

I created a new query and followed your steps again.  It worked!  Thank you such much.

Helpful resources

Announcements
Super Users of the Quarter - Q2 2020

Super Users of the Quarter - Q2 2020

Who are our Super User Superstars? Who made it to the top of the leaderboards? Get the answers!

June 2020 Community Highlights

June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors