I'm looking to map certain regions to one of two countries (there are four regions for the U.S. and 3 for Canada).
I'm working in PowerQuery (using Office ProPlus 365) and would like to create a report which displays a matrix of age by gender by race by country by region. The way I'm pulling in my data is by creating a connection to a folder with the file containing raw test-taker data. The reason being is that there are weekly updates for the data set (a new data set will be created containing new respondent data as well preserving previous respondent IDs).
It seems like a fairly straightforward activity, but the way I currently have my data set up and loading it into a pivot table, the 7 total regions will display for both the U.S. and Canada heading. The only solution I've come across is to unselect "show items with no data on columns" in the pivot table options, however, I would need this option checked off, as I would like to have all four regions for the U.S. displayed.
Essentially, I'm looking to only display regions relevant only to Canada and display those only relevant to the U.S. while preserving the layout/format of the pivot table. If there is no South data, for instance, I would like to keep that as a column, but only within the U.S., not Canadian header.