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))
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
- Load a blank table to the data model. Instructions can be found here - https://support.office.com/en-us/article/create-a-blank-table-in-power-pivot-4ba54776-f77b-4c3b-adc5-d44e8ae5abbf
- Create a calulated column with the DAX formula =DISTINCT('Receiptdump'[Receipt no])
- This will populate the column with a set of all the distinct receipt number entries and can be used to form relationships
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 |
So if I apply distinct (table) I will not be able to get the distinct table.
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.