Question on removing duplicates, via GUI and using Distinct formula

[acerNZ]

Experts 

I am trying to remove unique column elements and bit lost on seemingly simple things. help ?

1. GUI stuff: In the query editor, where do you find, remove duplicates in columns, I see this only on right click "Remove Duplicates" where can I find "Remove Duplicates" in the GUI, I could not find in the Query Editor's File, Home, Transform or Add Columns..

Are there some functionality only provided in Right click ?

2. Determined to get this, I added "Custom Column" and tried 2 different ways table.distinct and list.distinct and used the source input column listed (inserted), I get error in both ways saying 

Expression.Error: We cannot apply field access to the type Function.
Details:
Value=[Function]
Key=Data Key

what mistake, I am doing here? my colummn name is "Data key" and formula is Table.Distinct([Data Key]), tried with/without brackets "()" and the same for list.Distinct .. 

3. How to remove selected Rows in random places ? 

[Jimmy801 · Accepted solution]

Hello @acerNZ 

you cannot copy paste my code into yours. You can create a new blank query and paste it there to see all steps how it's working. You can either go to your query and use the GUI for create a distinct table (as shown in my screenshot) or to analyse my code and try to change your code accordingly (for sure not easy task if never done, but you can make it).

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[v-yingjl · Accepted solution]

Hi @acerNZ ,

1. Besides right click the column to use remove duplicates, you can also find it in the Home ribbon:

2. Table.distinct() is used for a table, List.distinct() is used for a list, you cannot use them to add a custom column in power query. Refer the corresponding documents about them with samples in the docuements.

3. I could think remove selected Rows in random places as select random rows, you can refer this article which introduces it in details that could help you: How to get a random sample of data with Power Query 

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[acerNZ]

Thanks a lot Yingjie Li

Oh cannot I have 2 replies as solution? You removal of rows is a solution, which I should try thank you. 

[Jimmy801]

Hello @acerNZ 

check out this code. I applied 2 different type of distinct (table and list)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8sgsSlVISSxJVYrViVZyT81LSS0CM138ncB0QEZ+XqohgmkEZtJRWywA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Data Key" = _t]),
    ChangeType = Table.TransformColumnTypes(Source,{{"Data Key", type text}}),
    AddedDistinctedTableInColumn = Table.AddColumn(ChangeType, "Custom", each Table.Distinct(ChangeType,"Data Key")),
    AddedDistictedListInColumn = Table.AddColumn(AddedDistinctedTableInColumn, "Custom.1", each List.Distinct(ChangeType[Data Key])),
    CreateDistinctTableSpecifiyingColumn = Table.Distinct(ChangeType, "Data Key")
    
in
    CreateDistinctTableSpecifiyingColumn

the distinct-function you can find left-click on the table

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[acerNZ]

@Jimmy801 Thanks a ton Jimmy

Please can you help me, I do not see new blank query for advanced editor, did you mean erase the existing code? 

2. I appended your code to the existing one and editor did not like, it expected eof ..

[Jimmy801 · Accepted solution]

Hello @acerNZ 

you cannot copy paste my code into yours. You can create a new blank query and paste it there to see all steps how it's working. You can either go to your query and use the GUI for create a distinct table (as shown in my screenshot) or to analyse my code and try to change your code accordingly (for sure not easy task if never done, but you can make it).

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[MattAllington]

The menu item is hidden under (I think) Transform, Reduce Rows, or Remove rows, or something like that. I think it is the home menu

adding a column will never help. These functions you mention generate new tables and lists. That is not what you need. 

[acerNZ]

Thank you @MattAllington 

Really this is putting me off track. Not finding menu is one thing but keen to know the why distinct formula is not working and where to use list and table. Any help would be appreciated.