Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Receipt no | BPIS NO | BPIS Date | Mapping status | Count |
C0093521800662 | 1013x | 30-01-2018 | MAPPED | 3 |
C0093521800662 | 1013x | 30-01-2018 | MAPPED | 3 |
C0093521800662 | 1013x | 30-01-2018 | MAPPED | 3 |
C00935218006623 | 1014x | 29-01-2018 | MAPPED | 2 |
C00935218006623 | 1014x | 29-01-2018 | MAPPED | 2 |
C00935218006625 | 1015x | 28-01-2018 | MAPPED | 1 |
C00935218006626 | 1016x | 28-01-2018 | MAPPED | 1 |
Receipt no | BPIS NO | BPIS Date | Mapping status | Count |
C00935218006625 | 1015x | 28-01-2018 | MAPPED | 1 |
C00935218006626 | 1016x | 28-01-2018 | MAPPED | 1 |
Solved! Go to Solution.
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,
If what you want to acheive is a table which contains a set of all the distinct receipt numbers you can do the following
Power Pivot
Power BI
If implementing this in Power BI this is slightly easier. You can follow the steps above replacing Step 1 with creating a calculated table and inputting the measure.
Hope this helps.
Kris
Hi, thanks for the reply.
Receipt no | BPIS NO | BPIS Date | Mapping status | Count |
C00935218006625 | 1015x | 28-01-2018 | MAPPED | 1 |
C00935218006625 | 1016x | 28-01-2018 | MAPPED | 1 |
Hi @Kumshan450,
I see. With the table you have posted above you will not be able to use this table to create a relationship on Receipt no as you have multiple instances of the same receipt number. It will help if you can explain why you need to create the relationship and then I will be able to advise, as I can see multiple ways of achiving multiple goals here.
Thanks!
Kris
Hi @Kumshan450,
I'll talk generally - The easiest way create the table that you desire would be to use the query editor. Load the source table again (as you would have to initially load this to your model). In the query editing mode highlight the 3 columns that you wish to keep distinct rows (hold Ctrl whn clicking each one to highlight multiple) and select Remove Rows > Duplicate Values as shown below
This will give you a table of all distinct Receipt no/BPIS NO/BPIS Dates.
Let me know how you get on.
Cheers,
Kris
Hi,
I am not using power query to remove duplicates as
Removing duplicates in power query subsequently slows the process of loading the query in power pivot table.
Hi @Kumshan450,
If you only wish to use DAX and you are using Power Pivot (and not Power BI), the only way I can see to do this would be to
Let me know if this suffices.
Thanks,
Kris
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,
Perfect! I see that you must be using Power BI then and not Power Pivot?
Congrats and Kudos,
Kris
Yes i am using power BI
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
Hi, It will not filter out the below sample which is also a duplicate
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.
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.
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.
User | Count |
---|---|
102 | |
88 | |
78 | |
71 | |
69 |
User | Count |
---|---|
113 | |
99 | |
97 | |
72 | |
68 |