Forum Discussion
Question on removing duplicates, via GUI and using Distinct formula
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.
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
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 ..
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