cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Rygaard
Resolver I
Resolver I

Count rows (duplicates) in Query Editor

I have a table "Sales _Offers"

and i have a column "Name_trimmed_striped"

 

I want to add a new column that make a count for each line how many times it occures

(NOT IN DAX, but still in the import Power Query Editor)  

 

customer 

Name_trimmed_stripedthe column i want to make
aroad1232
avestroad4121
vroad1232
bvestroad412341651

 

 

it would be easy to use groupe by, but i need all the lines intact.

it would also be easy to make a new table then group by in that, then merge to 2 to get a value looked up  (but since its 19 million lines this seems like a bad idea)

 

tried to use Table.RowCount   and im guessing that is what i need.. but cant fugure out how to make it work

3 REPLIES 3
Rygaard
Resolver I
Resolver I

I did not accept this as a solution, sinces it does exactly what i did not want,  It was NOT supose to happen in DAX but during import.  - also if you want to do it in dax, then there is a much shortwer and more efficiant way to do it using "Earlier"

 
 

 

 

 

Count number of occurences = CALCULATE(COUNT('Sales_Offers'[Name_trimmed_striped]), 'Sales_Offers'[Name_trimmed_striped] = EARLIER('Sales_Offers'[Name_trimmed_striped]))

 

 

 

But thank you for taking time to try and help me - and sorry if I did not formulate the question precise in the opening question @v-binbinyu-msft 

This is still not the solution i wanted since i wanted it in import, but it is better than the one that was accepted as a solution (not by me)

v-binbinyu-msft
Community Support
Community Support

Hi @Rygaard ,

Please try below steps:

1. below is my test table

Table:

vbinbinyumsft_0-1670300062230.png

2. create measure with below dax formula

Measure =
VAR cur_nts =
    SELECTEDVALUE ( 'Table'[Name_trimmed_striped] )
VAR tmp =
    FILTER ( ALL ( 'Table' ), 'Table'[Name_trimmed_striped] = cur_nts )
VAR tmp1 =
    SUMMARIZE ( tmp, [Name_trimmed_striped], "CTR", COUNT ( [Customer] ) )
VAR _str =
    CONCATENATEX ( tmp1, [Name_trimmed_striped] & [CTR], "" )
VAR num =
    LEN ( cur_nts )
VAR _val =
    MID ( _str, num + 1, LEN ( _str ) )
RETURN
    _val

3. add a table visual with fields and measure

vbinbinyumsft_1-1670300148137.png

Please refer the attached .pbix file.

 

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.