Forum Discussion
Remove duplicates selectively based on priority order
Thanks Zubair. Sorry I didn't specify that B1 in my data could appear on any row -- not necessarily on the first row all the time. That makes the remove duplicates function not suitable for my requirement.
I got help from a colleague and ended up learning a bit of M to get the job done. My code looks something like this:
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"B1 rows" = Table.Distinct(Table.SelectRows(Source, each ([Col B] = "B1")), {"Col A"}), #"A1 list" = Table.Column(#"B1 rows", "Col A"), #"Non B1 Rows" = Table.Distinct(Table.SelectRows(#"Table 1", each not List.Contains(#"A1 list", [DNS])), {"Col A"}), #"Appended Query" = Table.Combine({#"B1 rows", #"Non B1 Rows"}) in #"Appended Query"The exception join (not List.Contains) must not be efficient because my query runs 10 minutes or longer. But it gets the job done, and that's what i care about.
If your data is sorted (as in your sample data) i.e. B1 is the first row for each unique ColA item
you simple have to select Col A>>right click>>remove duplicates
Thanks Zubair. Sorry I didn't specify that B1 in my data could appear on any row -- not necessarily on the first row all the time. That makes the remove duplicates function is not suitable for my requirement.
I got help from a colleague and ended up learning a bit of M to get the job done. My code looks something like this:
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"B1 rows" = Table.Distinct(Table.SelectRows(Source, each ([Col B] = "B1")), {"Col A"}), #"A1 list" = Table.Column(#"B1 rows", "Col A"), #"Non B1 Rows" = Table.Distinct(Table.SelectRows(#"Table 1", each not List.Contains(#"A1 list", [DNS])), {"Col A"}), #"Appended Query" = Table.Combine({#"B1 rows", #"Non B1 Rows"}) in #"Appended Query"The exception join (not List.Contains) must not be efficient because my query runs 10 minutes or longer. But it gets the job done, and that's what i care about.
Thanks Zubair. Sorry I didn't specify that B1 in my data could appear on any row -- not necessarily on the first row all the time. That makes the remove duplicates function not suitable for my requirement.
I got help from a colleague and ended up learning a bit of M to get the job done. My code looks something like this:
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"B1 rows" = Table.Distinct(Table.SelectRows(Source, each ([Col B] = "B1")), {"Col A"}), #"A1 list" = Table.Column(#"B1 rows", "Col A"), #"Non B1 Rows" = Table.Distinct(Table.SelectRows(#"Table 1", each not List.Contains(#"A1 list", [DNS])), {"Col A"}), #"Appended Query" = Table.Combine({#"B1 rows", #"Non B1 Rows"}) in #"Appended Query"The exception join (not List.Contains) must not be efficient because my query runs 10 minutes or longer. But it gets the job done, and that's what i care about.