Forum Discussion
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 1 4
104 K 2
104 K 2
104 K 2 4
105 K 2
105 K 2
105 K 2 4
106 K 2
106 K 2
106 K 2 4
104 K 3
104 K 3
104 K 3 4
105 K 3
105 K 3
105 K 3 4
106 K 3
106 K 3
106 K 3 4
Hello, dear forum minds need a help on one task, i would like my data looks like column "Idea", and the point is to insert value "4" after cycle of column "Up" is breaking on unique number, somtimes its getting repited and then need to be taken to acount column "Down". Thank you great mind in advance!
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
7 Replies
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 CustomHi 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 Columns
