I need to create a M:M link between two tables with similar structures. (One is historical data. The other is forecast data.) The data is categorized by Market and Submarket, like this:
| Market | Submarket | Region | Sales |
| Retail | Grocery | NA | 247 |
| Retail | Grocery | EU | 656 |
| Retail | Grocery | AP | 723 |
| Retail | Department Stores | NA | 912 |
| Retail | Department Stores | EU | 719 |
| Retail | Department Stores | AP | 206 |
| Retail | Online | NA | 861 |
| Retail | Online | EU | 818 |
| Retail | Online | AP | 919 |
| Financial | Banking | NA | 231 |
| Financial | Banking | EU | 326 |
| Financial | Banking | AP | 299 |
| Financial | Capital Markets | NA | 288 |
| Financial | Capital Markets | EU | 982 |
| Financial | Capital Markets | AP | 287 |
| Financial | Insurance | NA | 243 |
| Financial | Insurance | EU | 805 |
| Financial | Insurance | AP | 534 |
What I want is a table with distinct values of submarket grouped by market, like this:
| Market | Submarket |
| Financial | Banking |
| Financial | Capital Markets |
| Financial | Insurance |
| Retail | Department Stores |
| Retail | Grocery |
| Retail | Online |
I am not trying to summarize any values. I just want the hierarchical data structure. I've tried the SUMMARIZE and CALCULATETABLE functions, but I'm not able to get the result I'm looking for.
