Forum Discussion
Replace duplicated value counting two columns...
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
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
Hi Anonymous ,
Thank you for the quick reply, but now I think I posted this request in the wrong branch because I don't know how to apply or even test your answer (dummy me) each time I paste this bit of code in the advanced editor, I get errors...
I did some research and added a column "sh" with formula, but it worked till column "down" changed value, so that's why I was considering a distinct value based on two columns. Maybe there is a way to work on this direction...sh = VAR a = CALCULATE(MAX('testFile'[Up]),FILTER('testFile','testFile'[Index]<EARLIER('testFile'[Index]))) RETURN IF('testFile'[Up]<>a,8,0)sh Up Down Index Idea 8 104 K 1 1 4 0 104 K 1 2 0 104 K 1 3 8 105 K 1 4 4 0 105 K 1 5 0 105 K 1 6 8 106 K 1 7 4 0 106 K 1 8 0 106 K 1 9 8 104 K 2 10 4 8 104 K 2 11 8 104 K 2 12 8 105 K 2 13 4 8 105 K 2 14 8 105 K 2 15 0 106 K 2 16 4 0 106 K 2 17 0 106 K 2 18 8 104 K 3 19 4 8 104 K 3 20 8 104 K 3 21 8 105 K 3 22 4 8 105 K 3 23 8 105 K 3 24 0 106 K 3 25 4 0 106 K 3 26 0 106 K 3 27 FYI - Anonymous 's code works fine. See these instructions on how to use them in Power Query.
How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.You should do this in Power Query if possible. Adding a calculated column is not best practice. In general, try to avoid calculated columns. There are times to use them, but it is rare. Getting data out of the source system, creating columns in Power Query, or DAX Measures are usually preferred to calculated columns. See these references:
Calculated Columns vs Measures in DAX
Calculated Columns and Measures in DAX
Storage differences between calculated columns and calculated tables
SQLBI Video on Measures vs Calculated ColumnsThank you edhans , can I steal your instruction🤣
Doro if you just need distinct combination, no matter it comes the first or the last, then yes, the way I sent earlier should work. You need paste in Advanced Editor via a Blank query. And Ed's suggestion is very good, go with M instead DAX calculated columns/tables applies for most of the senarios.
