Forum Discussion
Doro
4 years agoFrequent Visitor
Replace duplicated value counting two columns...
Up Down Idea 104 K 1 104 K 1 104 K 1 4 105 K 1 105 K 1 105 K 1 4 106 K 1 106 K 1 106 K ...
- Anonymous4 years ago
Hi Doro
I am not sure if you want to keep distinct values for Up + Down? It is very easy to identify the first combination, just add Index, and if you don't need the rest, just keep the first one as not sure how many for each combination...let me know if you need something else
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQw8T60QElH6dACQ6VYHQICQBoiZIquBo8AXJMZuho8AnBNJt5AASPcHCQnIclh4SA5A0kOCwfVamPcHFSrjXFzUK02xs0BKYwFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Up " = _t, #" Down " = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Up ", type text}, {" Down ", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Up ", " Down "}, {{"allrows", each Table.AddIndexColumn(_,"index",0,1) }}), #"Removed Other Columns" = Table.SelectColumns(#"Grouped Rows",{"allrows"}), #"Expanded allrows" = Table.ExpandTableColumn(#"Removed Other Columns", "allrows", {"Up ", " Down ", "index"}), Custom = Table.TransformColumns(#"Expanded allrows", {{"index", each if _=0 then _ else null}}) in Custom