Mapping Certain Regions to Certain Countries in PowerQuery

[Darko_Giac]

Hi all,

I'm currently working with a fairly large data set (a sample to which there is a link below).

Mapping in PowerQuery and Displaying in PowerPivot

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. 

Any suggestions would be greatly appreciated.

[v-shex-msft · Accepted solution]

Hi @Darko_Giac,

Maybe you can try to use below query to 'unpivot columns' and clean up the area prefix.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rdRNC4IwGAfwryKeG+hetI4hHTr0QhcP0eEhVw1kgQp9/VYatdojtQRhA/n/mP9nuN2GjIejcAGlNMvyXDUnCXVj9p9PPCbxxKz5STUy3I1MNvkjmz6zt50qLtIVdiQn3yVpRCi3kjzyTtLfkivTRdUmuZV8e3KjBBloKBToYCOP6qzdjPBhBGGRxYh4GCZyMzNwAl03HTOtzcuWoR4ME+Te6QvDhmHwSWVSNxWUbswhIcP69UCJ34G4IMIumqaohI/9waxhrw5qH8zrEnTR3QCGdP6H6HPDRUzuLbnF8dAi9/nqfhGfcY+YkkSgos+s+0W7R/TnhyO7Kw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Response ID" = _t, Gender = _t, US_NorthEast = _t, US_Midwest = _t, US_South = _t, US_West = _t, CAN_East = _t, CAN_Central = _t, CAN_West = _t, Age = _t, Race = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Response ID", Int64.Type}, {"Gender", type text}, {"US_NorthEast", type text}, {"US_Midwest", type text}, {"US_South", type text}, {"US_West", type text}, {"CAN_East", type text}, {"CAN_Central", type text}, {"CAN_West", type text}, {"Age", type text}, {"Race", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Response ID", "Gender", "Age", "Race"}, "Attribute", "Value"),
    TextToRemove= {"US_","CAN_"},
    Cleaned = Table.TransformColumns(#"Unpivoted Columns",{{"Attribute", each List.Accumulate(TextToRemove,_,(String,Remove) => Text.Replace(String,Remove,""))}})
in
    Cleaned

Regards,

Xiaoxin Sheng

[Darko_Giac]

Thank you @v-shex-msft! Worked really well!

D.