Set duplicate rows to 0 (NOT DELETE)

[Poisedon]

Hi all,

In query editor i have a table like this

Namefile        Value           

 a.xml              12

 a.xml              12

 a.xml              12

 b.xml               8

 c.xml               5

 c.xml               5

I need to set to 0 all duplicate values for each file so it is more comfortable after applying query for my calculations. I CAN'T DELETE DUPLICATES BECAUSE I HAVE OTHER COLUMNS WITH DATA FOR EACH FILE SO DON'T TELL ME THIS SOLUTION. Thanks.

Final output must be:

Namefile        Value           

 a.xml              12

 a.xml               0

 a.xml               0

 b.xml               8

 c.xml               5

 c.xml               0

[Stachu]

try this (replace source)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTI0UorVwcVMAjItwKxkIMsUmRULAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Namefile = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Namefile", type text}, {"Value", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
    #"Grouped Rows" = Table.Group(#"Added Index", {"Namefile", "Value"}, {{"Index", each List.Min([Index]), type number}}),
    #"Merged Queries" = Table.NestedJoin(#"Added Index",{"Namefile", "Index"},#"Grouped Rows",{"Namefile", "Index"},"Grouped Rows",JoinKind.LeftOuter),
    #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"Value"}, {"NewValue"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Grouped Rows",{"Index"}),
    #"Sorted Rows" = Table.Sort(#"Removed Columns",{{"Namefile", Order.Ascending}})
in
    #"Sorted Rows"

[Poisedon]

i can't replace source, there are so much code, maybe if you want i can give u my code with columns we're speaking about

[Stachu]

add a new query, then replace the Table with the name of your source table
the code does assume that the columns in question are named "Namefile" and "Value"

let
    Source = Table,
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
    #"Grouped Rows" = Table.Group(#"Added Index", {"Namefile", "Value"}, {{"Index", each List.Min([Index]), type number}}),
    #"Merged Queries" = Table.NestedJoin(#"Added Index",{"Namefile", "Index"},#"Grouped Rows",{"Namefile", "Index"},"Grouped Rows",JoinKind.LeftOuter),
    #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"Value"}, {"NewValue"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Grouped Rows",{"Index"}),
    #"Sorted Rows" = Table.Sort(#"Removed Columns",{{"Namefile", Order.Ascending}})
in
    #"Sorted Rows"

[Poisedon]

i have so many codes before this, i tried to copy this after all, but not worked, maybe because mine is in italian?
Can't u tell me step by step what to do without write code?

[Stachu · Accepted solution]

language version should not have any impact

this is the easiest way to do it

1) right click your original query, click Reference

2) go to the newly created query, go to Advanced Editor
3) remove last 2 rows

4) add "," to the last row in the code

5) paste this code below the existing one

    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
    #"Grouped Rows" = Table.Group(#"Added Index", {"Namefile", "Value"}, {{"Index", each List.Min([Index]), type number}}),
    #"Merged Queries" = Table.NestedJoin(#"Added Index",{"Namefile", "Index"},#"Grouped Rows",{"Namefile", "Index"},"Grouped Rows",JoinKind.LeftOuter),
    #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"Value"}, {"NewValue"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Grouped Rows",{"Index"}),
    #"Sorted Rows" = Table.Sort(#"Removed Columns",{{"Namefile", Order.Ascending}})
in
    #"Sorted Rows"

what happends conceptually is following:
1) add index to table

2) group by Filename and Value with Index aggergated as MIN
3) reference the original query and merge with the grouped one on all 3 columns (this will require changing the code in the formula bar, as it is referencing step within same query)
4) expand the value column

5) remove index

6) sort by filename