Forum Discussion
Create new table by filtering column value
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))
Yes i will check
Is there any I can create a new table from the existing table in power pivot table data model with count value =1. This will definitely give me an accurate distinct table.
using dax
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))
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
Its 20 lakh rows of data.
Also i dont want unique combinations. I want unique receipt no.
IF i apply the formula
Table = SUMMARIZE(Receiptdump,Receiptdump[Receipt no],Receiptdump[BPIS NO],Receiptdump[BPIS Date])
IT will show the unique combinations of receipt no|Receipdate|BPISno.
I want to remove all the duplicate receipt no and concentrate only on the unique receipt no.
I have mentioned in the post that there are also samples where receipt no are same but the BPIS no and dates are different. I dont want to filter this data. I want to remove the same.
Its 20 lakh rows of data.
Also i dont want unique combinations. I want unique receipt no.
IF i apply the formula
Table = SUMMARIZE(Receiptdump,Receiptdump[Receipt no],Receiptdump[BPIS NO],Receiptdump[BPIS Date])
IT will show the unique combinations of receipt no|Receipdate|BPISno.
I want to remove all the duplicate receipt no and concentrate only on the unique receipt no.
I have mentioned in the post that there are also samples where receipt no is same but the BPIS no and dates are different. I don't want to filter this data. I want to remove the same.
Hi Kumshan450,
Perfect! I see that you must be using Power BI then and not Power Pivot?
Congrats and Kudos,
Kris
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 no BPIS NO BPIS Date Mapping status Count C00935218006625 1015x 28-01-2018 MAPPED 2 C00935218006625 1016x 28-01-2018 MAPPED 2 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.
Yes i am using power BI