Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi guys,
I have a dataset and if there are duplicit values I want to remove them completely.
How can I do that? Standard "remove duplicates" keep one instance of every duplicate, and remove the others. I want to remove also this one instance
EG I have data set where are duplicates "asd" a "dfg"
ID | Count |
asd | 15 |
dfg | 7 |
vgt | 54 |
bht | 41 |
bju | 12 |
lop | 15 |
pol | 17 |
trf | 14 |
trt | 18 |
asd | 8 |
rrf | 14 |
ttq | 21 |
dfg | 17 |
And I want to get this (where are no duplicate IDs):
ID | Count |
vgt | 54 |
bht | 41 |
bju | 12 |
lop | 15 |
pol | 17 |
trf | 14 |
trt | 18 |
rrf | 14 |
ttq | 21 |
Thanks
Lukas
Solved! Go to Solution.
@Anonymous ,
In Power Query, Duplicate your table, then do a Group By on one table which will count the columns and you will delete any results that are not 1, then merge the tables together on ID, then expand so that you get your original count column, delete all but your original two columns.
The pictures are not in exact order, but number 1 is at the bottom
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel
Proud to be a Super User!
Hi guys,
thanks for help.
Nathaniel´s recommandations works for me. Because I have more columns in my original tables and many values are text.
Ashish_Mathur, just out of curiosity, is it possible to deal also with this type of situation? Beause grouping obviously works on numbers or text (count)?
Thanks
Lukas
Hi,
I do not understand your question.
Hi,
This M code works without duplicating the table
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSixOUdJRMjRVitWJVkpJSwdyzMHssvQSINvUBMxJygBxTAwhnKxSkBYjMCcnvwChvyA/B8SBGFBSlAbimEA5IAMMLcAciJ0QdhGKqpJCIMfIEMkxIMNiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Count = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Count", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"Count1", each Table.RowCount(_), type number}}),
Joined = Table.Join(Source, "ID", #"Grouped Rows", "ID"),
#"Filtered Rows" = Table.SelectRows(Joined, each [Count1] = 1),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Count1"})
in
#"Removed Columns"
Hope this helps.
Hi @Anonymous ,
You can implement your demand following steps below :
Step #1: Create a reference table, then merge the two tables on [ID].
Step #2: Expand the table, choose the [Count] to display to get column [Count.1].
Step #3: Group by the [ID] and [Count.1] to get new count column [Count].
Step #4: Filter row for [Count]=1.
Step #5: Remove Column [Count], then you can choose rename the [Count.1] or not.
You can refer to may test pbix.
Best Regards,
Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous ,
In Power Query, Duplicate your table, then do a Group By on one table which will count the columns and you will delete any results that are not 1, then merge the tables together on ID, then expand so that you get your original count column, delete all but your original two columns.
The pictures are not in exact order, but number 1 is at the bottom
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
98 | |
78 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |