Forum Discussion

Sofinobi's avatar
Sofinobi
Helper IV
2 years ago
Solved

delete duplicate in a column without deleting the rows

Hi all,

please i need help,

i have a sales table, that has in "Stock" column duplicate values, i'm looking if there is a solution to delete duplicate values without deleting all the row.

info; duplicate it depends on the "Product ID" columns, not in "Stock" (because in Stock we can have duplicate values but different Product ID, in this case its Ok)
(i have the solution in excel formula, but i need it in power query)
thank you so much.

sample_file.xlsx 
  

Need Help
  • ronrsnfld's avatar
    ronrsnfld
    2 years ago

    When tested using the Power BI tools, this code seems to execute much more rapidly than yours.

    • Group by Product ID
    • Add a Shifted stock column to the sub-table
    • If the contents of Stock and Shifted Stock are the same, then replace Stock with a null
    • Remove the Shifted column
    • Re-expand the table

     

    let
    Source = Excel.CurrentWorkbook(){[Name="Table9"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer ID", type text}, {"Product ID", Int64.Type}, {"Sales", Int64.Type}, {"Stock", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Product ID"}, {
        {"all", (t)=> 
            let 
                #"Add Shifted" = Table.FromColumns( 
                        Table.ToColumns(t) & 
                        {{null} & List.RemoveLastN(t[Stock],1)},
                        Table.ColumnNames(t) & {"Shifted"}),
                #"Null Dup Stock" = Table.ReplaceValue(
                    #"Add Shifted",
                    each [Stock],
                    each [Shifted],
                    (x,y,z)=> if y = z then null else y,
                    {"Stock"}),
                #"Remove Shifted" = Table.RemoveColumns(#"Null Dup Stock","Shifted")
            in 
                #"Remove Shifted",
            type table[Customer ID=text, Product ID=Int64.Type, Sales=Int64.Type, Stock=Int64.Type]}
        
        
        }),
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Product ID"}),
    #"Expanded all" = Table.ExpandTableColumn(#"Removed Columns", "all", Table.ColumnNames(#"Changed Type"))
in
    #"Expanded all"

    Results from your data

     

     

     

     

     

  • dufoq3's avatar
    dufoq3
    2 years ago

    Hi Sofinobi, different approach here.

     

    Result

     

    let
    Source = Excel.Workbook(File.Contents("C:\Downloads\2024 Test duplicate.xlsx"), null, true),
    Sales_Table = Source{[Item="Sales",Kind="Table"]}[Data],
    GroupedRows = Table.Group(Sales_Table, {"Product ID"}, {{"All", each 
        [ a = Table.RemoveColumns(_, {"Stock"}),
          b = Table.FromColumns(Table.ToColumns(a) & {{[Stock]{0}?} & List.Repeat({null}, Table.RowCount(a)-1)}, Value.Type(_))
        ][b], type table}}),
    CombinedAll = Table.Combine(GroupedRows[All])
in
    CombinedAll

     

11 Replies

  • ToddChitt's avatar
    ToddChitt
    Super User
    2 years ago

    Question: Why should the "Stock" value of 1,407 for Product ID 50159 be attributed to the Sales to Customer 3 instead of Customer 4 or 5? 

     

    Honestly, you should strive to break your model out into three different tables: Customer (dimension), Product (dimension) and Sales (fact). 

    That "Stock" column is an attribute of the Product dimesnion and has no business in the Sales fact table. 

     

     

    • Sofinobi's avatar
      Sofinobi
      Helper IV
      2 years ago

      hi ToddChitt  thank you for your answer,

      the "Stock" value depends on the "Product ID", it doesn't matter if it is attributed to Costomer 3 or 4 or 5.
      i agree with you about the model, (it's already a model with fact and Dim Tables), but for our business need, i have to send this "Table Situation" monthely.

      • ToddChitt's avatar
        ToddChitt
        Super User
        2 years ago

        Ah, OK, that is a different question. It is not about removing values from the SOURCE DATA, it is about removing values from a visualization. (Hint: this post probably belongs in the Desktop forum.)

         

        I suggest you look into two DAX features:

        Window Functions WINDOW function (DAX) - DAX | Microsoft Learn

        and Visual Calculations Using visual calculations in Power BI Desktop - Power BI | Microsoft Learn

         

        I would set up the table as you have shown with the four columns. Next, create a Visual Calculation (column) that looks at the previous (Hint: LAG) Product ID. Finally, create a second Visual Calculation that has IF/THEN logic like this: IF ([Procuct ID] <> [Previous Product Id], [Stock], "")

        You could probably wrap the whole thing in one big DAX calculation statement but I like to break out the components during development and testing.

        Hope that helps

    • Sofinobi's avatar
      Sofinobi
      Helper IV
      2 years ago

      i found a solution, but its really energivore. if there is a light version of this M code

       

      #"Type modifié" = Table.TransformColumnTypes(Source,{ {"Product ID", Int64.Type}}),
      #"Added Index" = Table.AddIndexColumn(#"Type modifié", "Index", 0, 1, Int64.Type),
      #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each List.PositionOf(#"Added Index"[Product ID],[Product ID])),
      #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each if [Index] = [Custom] then [Stock] else 0
      )
      in
      #"Added Custom1"

      • ronrsnfld's avatar
        ronrsnfld
        Super User
        2 years ago

        When tested using the Power BI tools, this code seems to execute much more rapidly than yours.

        • Group by Product ID
        • Add a Shifted stock column to the sub-table
        • If the contents of Stock and Shifted Stock are the same, then replace Stock with a null
        • Remove the Shifted column
        • Re-expand the table

         

        let
    Source = Excel.CurrentWorkbook(){[Name="Table9"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer ID", type text}, {"Product ID", Int64.Type}, {"Sales", Int64.Type}, {"Stock", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Product ID"}, {
        {"all", (t)=> 
            let 
                #"Add Shifted" = Table.FromColumns( 
                        Table.ToColumns(t) & 
                        {{null} & List.RemoveLastN(t[Stock],1)},
                        Table.ColumnNames(t) & {"Shifted"}),
                #"Null Dup Stock" = Table.ReplaceValue(
                    #"Add Shifted",
                    each [Stock],
                    each [Shifted],
                    (x,y,z)=> if y = z then null else y,
                    {"Stock"}),
                #"Remove Shifted" = Table.RemoveColumns(#"Null Dup Stock","Shifted")
            in 
                #"Remove Shifted",
            type table[Customer ID=text, Product ID=Int64.Type, Sales=Int64.Type, Stock=Int64.Type]}
        
        
        }),
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Product ID"}),
    #"Expanded all" = Table.ExpandTableColumn(#"Removed Columns", "all", Table.ColumnNames(#"Changed Type"))
in
    #"Expanded all"

        Results from your data

         

         

         

         

         

  • dufoq3's avatar
    dufoq3
    Community Champion
    2 years ago

    Hi Sofinobi, different approach here.

     

    Result

     

    let
    Source = Excel.Workbook(File.Contents("C:\Downloads\2024 Test duplicate.xlsx"), null, true),
    Sales_Table = Source{[Item="Sales",Kind="Table"]}[Data],
    GroupedRows = Table.Group(Sales_Table, {"Product ID"}, {{"All", each 
        [ a = Table.RemoveColumns(_, {"Stock"}),
          b = Table.FromColumns(Table.ToColumns(a) & {{[Stock]{0}?} & List.Repeat({null}, Table.RowCount(a)-1)}, Value.Type(_))
        ][b], type table}}),
    CombinedAll = Table.Combine(GroupedRows[All])
in
    CombinedAll

     

    • Sofinobi's avatar
      Sofinobi
      Helper IV
      2 years ago

      thank you dufoq3  for your contribution, thats works perfectly, i'll test the speed with the fist code of ronrsnfld 

      thank you so much my friend