Joining tables - flag no join as 'other'

[bo_afk]

I have a sales table that shows date, sales and marketID. I then have another table which shows marketID and countryname.

My tables are joined by marketID to retrieve the countryname. However, for any marketID that isn't matched in the market table, I would like to flag these as 'Other'. Is this possible?

Update: my market table also has a conversion figure that I use to apply to the sales figure. For any markets in the sales table that don't appear in the market table, I would like these to be grouped under 'other' so that I can convert the sales figure by the 'Other' conversion figure.

Thanks!

[paulvans182]

Hi @bo_afk ,

I am not sure if I have missed something in your query, but perhaps in the query editor you could create a new Conditional Column called 'New Market ID' and, use the formula: 

= Table.AddColumn(#"Expanded Sheet2", "Custom", each if [#"Sheet2.Countryname "] = null then "Other" else [Market ID])

This would then be able to replace your original Market ID column. 

The way I tested this was to create two tables as you described, and merged the queries with a Left Outer Join.

"Sheet2" was the Market ID, Countryname table you described

Hope this helps, or if I not fully understanding your question, please let me know?

Kind regards,

Paul

[bo_afk]

Hi @paulvans182 @Anonymous , thanks for your resposes. There is an update to my query.

My market table also has a conversion figure that I use to apply to the sales figure. For any markets in the sales table that don't appear in the market table, I would like these to be grouped under 'other' so that I can convert the sales figure by the 'Other' conversion figure.

Does this change anything to the suggestions you provided?

Thanks again!

[Anonymous · Accepted solution]

@bo_afk 

Column = 
VAR _conversionrate = RELATED(Market[conversionfigure])
RETURN IF(_conversionrate=BLANK(),1,_conversionrate)

Instead of Country put the conversion figure column name and in place of other put the default number that you want 

[Anonymous]

@bo_afk Hope there is many to 1 relationship between Sales and market table please create following calculated column in sales table

Column = 
VAR _country = RELATED(Market[Country])
RETURN IF(_country=BLANK(),"Other",_country)