Forum Discussion

Kumshan450's avatar
Kumshan450
Helper III
8 years ago
Solved

Create new table by filtering column value

Hi,    I have created a calculated column which will count rows that are duplicate.   Count = CALCULATE(countrows(Receiptdump),ALLEXCEPT(Receiptdump,Receiptdump[RECEIPT_NO]))   Receipt no ...
Need Help
  • Kumshan450's avatar
    Kumshan450
    8 years ago

    Hi I Finally got the answer, 

     

    I created the new table by applying the formula using calculate table function. 

     

    Distinctreceiptdump = CALCULATETABLE(Receiptdump,filter(Receiptdump,Receiptdump[Count]=1))

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Hi Kumshan450,

 

I am not sure you are able to create a calculated table of multiple columns in Power Pivot. In Power BI you can create a calculated talble to do this using the DAX formula

 

Table = SUMMARIZE(Receiptdump,Receiptdump[Receipt no],Receiptdump[BPIS NO],Receiptdump[BPIS Date])

 

Using Power Pivot and DAX I'd suggest trying my method above. Otherwise maybe biting the bullet and creating this in the query editor. How many rows of data are we talking that causes the refresh to take so long?

 

Best,

Kris

Kumshan450's avatar
Kumshan450
Helper III
8 years ago

Hi, It will not filter out the below sample which is also a duplicate

 

Hi, I want to mention that there are also samples where receipt no is same but the BPIS no in the table are different. 
 
Receipt noBPIS NOBPIS DateMapping status Count
C009352180066251015x28-01-2018MAPPED2
C009352180066251016x28-01-2018MAPPED2

 

However, I applied the calculate table function which is giving me distinct values. 

 

Distinctreceiptdump = CALCULATETABLE(Receiptdump,filter(Receiptdump,Receiptdump[Count]=1))

 

Why am I totally against removing duplicates in power query stage? Removing duplicate step dont fit large volume of data. I am handlling around 20 lakh rows of data. IF suppose i add additional months and refresh OH god I have to wait for months to load the query in data model.