Forum Discussion
Set duplicate rows to 0 (NOT DELETE)
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
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 rows4) 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 table2) 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 column5) remove index
6) sort by filename
5 Replies
- Stachu
Community Champion
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"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"
