Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Remove duplicates removing non duplicates

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!

 

Need Help

1 Reply

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    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