Remove duplicate column

[Anonymous]

Hi,

I want to see distinct values of a column in my table. Also I want to see the rest of the columns as well using distinct values for only one column.

I tried removing the duplicates in power query editor but when I applied the changes i got this error "object reference not set to an instance of an object"

Please let me know if I used DAX or I can do it using query editor

[Anonymous]

Hi @Anonymous 

Could you tell me if your problem has been solved? If it is, kindly Accept the helpful reply as a solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

Best Regards,

Rico Zhou

[amitchandak]

@Anonymous ,

New Table = distinct(Table)

or New table = summarize( Table, Table[Col1] ,,,,) give all columns

https://radacad.com/aggregated-table-in-power-bi-summarize-function-in-dax

for object reference - https://community.powerbi.com/t5/Issues/Object-reference-not-set-to-an-instance-of-an-object/idi-p/981144

[DataZoe]

@Anonymous You have a few approaches to choose from. 

In DAX you can create a calculated table:

Distinct Values = summarizecolumns('Table'[Value])

or

Distinct Values = Summarize('Table','Table'[Value])

Or just use a table visualization, which will dedup a list when you put in any field.

To get a distinct count of a items in a column:

Distinct = distinctcount('Table'[Value])

I'm not sure I've seen that error you are seeing in the PowerQuery window, but you should be able to remove duplicate values there too with the remove duplicates transformation. 

[Anonymous]

@DataZoe    Distinct Values = Summarize('Table','Table'[Value])

I tried using this formula but i get only one column and I want to see thes rest othe columns as well since I have to create measures and calculated columns in this table.

[amitchandak]

@Anonymous , Try distinct

New Table = distinct(Table)

In summarize give all column names. refer Post I shared last

[Anonymous]

@amitchandak  I tried doing both but it doesn't work, it gives me the same table as it is

[Greg_Deckler]

@Anonymous - You will need to aggregate and use SUMMARIZE or GROUPBY:

Table =  SUMMARIZE('Table',[Column you want distinct],"Column1",MAX([Column1]),"Column2",MAX([Column2]))

Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2. 

[Anonymous]

@Greg_Deckler This worked! Thank you

[Greg_Deckler]

@Anonymous Boom!