cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Ross73312 Super Contributor
Super Contributor

Re: Identify duplicates and show up records

Do you have any nulls (or blanks) on any rows for the column LAK_NUM?

If you do, you might want to add an IF statement into your Variable to check for blanks and use a 0 value in its place.  Something like

 

LAK_NUM = IF (ISBLANK([LAK_NUM]), 0, [LAK_NUM]


   

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

Proud to be a Datanaut!


   


gtamir Member
Member

Re: Identify duplicates and show up records

There are no blanks in the column. 

Why it says there in no current raw?

Ross73312 Super Contributor
Super Contributor

Re: Identify duplicates and show up records

Without seeing your dataset that would be difficult to say.  Generally i'd check things like:

 

  • Did i spell the column name correctly?
  • Is the column in this specific table, and not another table
  • Is there a single blank row in my table
  • Is there a null or blank value in any of my rows for that column

 

From the data area of Power BI, you can copy the table and paste into Excel.  I find that helps with the trouble shooting.  Follow the data and it should start to come clear what is out of the ordinary.


   

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

Proud to be a Datanaut!


   


Anonymous
Not applicable

Re: Identify duplicates and show up records

Hi Guys,

I couldn't get this to work for me. When I created my expressions based on my data

Count [#"HAWB/HBL"] =
Var HAWB/HBL = [#"HAWB/HBL"]
RETURN

CALCULATE(COUNTROWS('Your Table'),
    all('Your Table'),
    'Your Table'[#"HAWB/HBL"] = 'HAWB/HBL')

The syntax error seems to pick on "var "hawb/hbl". I have tried without parethatsise as well and get the same issue.

 

Please can you help

Olanna Visitor
Visitor

Re: Identify duplicates and show up records

Works perfectly

Anonymous
Not applicable

Re: Identify duplicates and show up records

@Ross73312  hi there, I just came across this post and it works exactly as suggested. thanks for that. My question is how I could use this or an equivalent in power query editor. I would prefer to add the calculated column in this environment because of subsequent logic I want to build into my table. 

 

thanks

Ross73312 Super Contributor
Super Contributor

Re: Identify duplicates and show up records

A find and replace of null might do what you are chasing.

Go to your "Replace Values" and write 'null'  (without quotes) into the top box and then put 0 in the other box.

 

Is that the part of the post you wanted a Power Query version for?


   

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

Proud to be a Datanaut!


   


Anonymous
Not applicable

Re: Identify duplicates and show up records

@Ross73312 

hmm, no I don't think so. I'm looking for a query editor version to count the number of occurrences of a value of all rows in the table.  It's this part of the post I am looking for an alternative for in query editor. 

 

Count Emails = 
Var Emails = [Emails]
RETURN

CALCULATE(
    COUNTROWS('Your Table'),
    all('Your Table'),
    'Your Table'[Emails] = Emails

 

Ross73312 Super Contributor
Super Contributor

Re: Identify duplicates and show up records

If you are wanting to do a count, you could use the Grouping function.  That will produce a count for you, but will summarize the table you use it in.  If you don't want to upset your existing data, just create a new query to reference the old query and do the grouping in the new table.  This table could be joined in the model.

 

The grouping function is found on the ribbon as "Group By".  Tell it to group by the email address and use the "Count" function to create the summarised field.


   

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

Proud to be a Datanaut!


   


rogerdea Regular Visitor
Regular Visitor

Re: Identify duplicates and show up records

This works great for me.  How would i amend this to only perform the duplicate check for the year a record is related to?

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 308 members 3,343 guests
Please welcome our newest community members: