Forum Discussion
Add Conditional Column (tag with 0 or 1)
1.) For future requests - allways provide sample data and expected resuld based on that!
2.) If you don't know how to do it - read note below my post.
3.) If you don't know how to use my query also read note below my post.
New sample: Removes duplicates in [Column1] and [Column2] and keep rows where there are no duplicates.
Before
After
let Source = Table.FromRows({{"A", 10, 1}, {"A", 10, 2}, {"B", 50, 5}, {"B", 50, 6}, {"C", 600, 8}, {"C", 600, 9}}), RemovedFirstDuplicate = Table.Combine(Table.Group(Source, {"Column1", "Column2"}, {{"T", each if Table.RowCount(_) = 1 then _ else Table.Skip(_), type table}})[T]) in RemovedFirstDuplicate
Start by adding an index column:
I started with this:
Then added an index column:
Then add another colum to show if it is odd or even:
Then filter only the 0 if you only want to keep the first.
Start with a right-click on any of the cells containg 0:
I leave removing the additional columns to you 😊
Here the full M Code. Replace the first 2 steps with your own ...
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclSK1YlWSgKTyWAyBUymgsk0MJmuFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each Number.Mod([Index],2)),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each [Custom] = 0)
in
#"Filtered Rows"
Did I answer your question? Then please mark my post as the solution and make it easier to find for others having a similar problem.
If I helped you, please click on the Thumbs Up to give Kudos.
Kees Stolker
A big fan of Power Query and Excel
HI Kees, thank you for your response as well. I do not know exactly where to place those codes. If could kindly read my response to DataNinja since it basically applies. My knowledge of PQ is pretty limited. thank you again for your response.
And if you start at the point where you want to remove the duplicates and follow my step by step instructions?That seems to me the best approach for you because you can check every step by itself and keep the coding to a minimum.