Forum Discussion
Question on removing duplicates, via GUI and using Distinct formula
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 ?
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
JimmyHi 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 LiIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
7 Replies
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 LiIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
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.
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.
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.
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 CreateDistinctTableSpecifiyingColumnthe 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
JimmyHello 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
