delete duplicate in a column without deleting the rows

[Sofinobi]

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 
  

[dufoq3]

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]

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

[dufoq3]

You're welcome.

[ToddChitt]

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]

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 · Accepted solution]

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

[Sofinobi]

thank you very much @ronrsnfld , your solution works perfectly, and so fast comparing to my first code.

thanks again.

[Sofinobi]

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]

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