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

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!


   


Highlighted
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?

Super User
Super User

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

RiceCake Regular Visitor
Regular Visitor

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

Super User
Super User

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!


   


RiceCake Regular Visitor
Regular Visitor

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

 

Super User
Super User

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!