Forum Discussion

sbadiger3's avatar
sbadiger3
Helper I
2 years ago

How to remove rows based on a condition

Hi everyone, I have a table in power query. There is column in the table by name 'order_line_id'. This column has duplicates which ideally shouldn't. I need to remove the rows with duplicate order_li...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
Gayatri_D05's avatar
Gayatri_D05
Resolver II
2 years ago

Hi sbadiger3 ,
A workaround to get the deseried output could be: 
Making an index column based on the id and then deleting the index id 1 where the occurenec would be more than one. I think using the logic you would be able to work through the data.


If your requirement is solved, please make THIS ANSWER as SOLUTION and help other users find the solution quickly. Please hit the LIKE button if this comment helps you. 😊


sbadiger3's avatar
sbadiger3
Helper I
2 years ago

Gayatri_D05 could you please help me understand how to create an index on that specific column?

  • Gayatri_D05's avatar
    Gayatri_D05
    Resolver II
    2 years ago

    Hi sbadiger3 ,
    I tried it out on the dummy data.

    According to our scenairo we want to keep the highlighted values i.e 15,25.
    AFter loading the data into Powerbi i just created a index column from the add column tab starting from 1. And then sorted my index column in a descending order

     After that going into the advanced query I simply added this step :
    #"Buffer table" = Table.Buffer (#"Sorted Rows")

    And then removed the duolicates from the id column which gave me the desired output:

    Sharing the Advanced editor query as well :


    let
    Source = Excel.Workbook(File.Contents("C:\Desktop\PowerBI Tasks\Data.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"id", Int64.Type}, {"values", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Sorted Rows" = Table.Sort(#"Added Index",{{"Index", Order.Descending}}),
    #"Buffer table" = Table.Buffer (#"Sorted Rows"),
    #"Removed Duplicates" = Table.Distinct(#"Buffer table", {"id"})
    in
    #"Removed Duplicates"

     

    Please try out the above steps as it worked on the dummy data.

    If your requirement is solved, please make THIS ANSWER as SOLUTION and help other users find the solution quickly. Please hit the LIKE button if this comment helps you. ğŸ˜Š