Remove rows based on an arbitrary rule

[jonoren]

Hi! so I have a table of currencies and their corresponding countries, and some countries have more than one currency (so they have more than one row in this table). Moreover, I need to merge this table with a "Countries" table such that every country has a single selected currency.

For example for Bhutan I want to choose BTN and not INR.

I can't perform this merger because in the Countries table the country is a primary key, and this merger creates multiple rows of the same country. In addition, I can't simply remove the INR rows because then I'd lose India. This seems very simple, but I can't seem to understand how to delete these specific rows.

Thank you!

[jonoren · Accepted solution]

Because the rule is completely arbitrary, and I know for a fact that the order of the rows, as well as the number of them, won't ever change, I solved this by simply creating an index column and filtering by it, manually removing the indexes I'm not interested in. In the end it was a very easy solution.. Thanks anyway guys! 

[jbwtp]

Hi @jonoren,

What is your selection logic to choose BTN vs INR? Can this be formalised to be programmed? If this is based on some "knowledge" you will need a translation table in the fashion that @MarCat mentioned.

Otherwise the method quite simple. Instead of choosing Expand when expending column choose Aggergate:

and then select anything (Count would do). Once the step is created change List.Count  in the formula to List.Last or some other computation that would produce a single result from a list of options.

If you need more help on this one, share your computation logic over selecting a paarticular currency from multiple options.

Thnaks,

John 

[MarCat]

You could add a column to the curreny table that acts as a flag for which currency to keep, filter on this flag and then merge.  Well, I think that would work.  Also if it doesn't matter which one the system keeps (could hardly imagine this though) then of course you could simply remove the duplicates based on the country field.