Remove duplicates removing non duplicates

[Anonymous]

Hi all,

I am trying to remove duplicates from a column in a table with many rows via query editor. However, the query removes many of the non-duplicates value as well. The data source is Salesforce object, and I have not done any modifications to the data before this step. 

Below is an example for illustration:

1. When I filtered the column with only one value which is non-duplicate...

...and then remove duplicates....

...the row is maintained.

2. However, if I switched the two queries, so Remove Duplicates first and then Filtered rows. Then it returns empty table. So the Remove Duplicates has apparently remove this row, even though it contains non-duplicates value.

Has anyone experienced this issue before? Any ideas on what is causing this?

Thanks!

[v-juanli-msft]

Hi @Anonymous

I can't reproduce your problem, could you provide more information?

I test with below table, 

id	id2	col3	code	date
1	1	FALSE	120	1/1/2018
2	1	TRUE	203	1/2/2018
3	2	TRUE	204	1/3/2018
4	2	FALSE	205	1/4/2018
5	3	TRUE	206	1/5/2018
6	3	FALSE	207	1/6/2018
7	4	TRUE	208	1/7/2018
8	5	FALSE	209	1/8/2018

Then write the code in Advanced editor, it works with this result

let
    Source = Excel.Workbook(File.Contents("C:\Users\maggiel\Desktop\case\12\12.20\12.20.xlsx"), null, true),
    Sheet6_Sheet = Source{[Item="Sheet6",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet6_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"id", Int64.Type}, {"id2", Int64.Type}, {"col3", type logical}, {"code", Int64.Type}, {"date", type date}}),
    new=Table.Distinct(#"Changed Type",{"id2"})
in
    new

 

Could you show an example for me to test?

Perhaps it is something wrong when you do filtering, if this problem doesn't solve,  Could you show an example for me to test?

Please update power bi desktop to the lastest version to see if this problem exists.

https://docs.microsoft.com/en-us/power-bi/desktop-latest-update

Best Regards

Maggie