cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Darko_Giac Regular Visitor
Regular Visitor

Mapping Certain Regions to Certain Countries in PowerQuery

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Mapping Certain Regions to Certain Countries in PowerQuery

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |

View solution in original post

2 REPLIES 2
Community Support Team
Community Support Team

Re: Mapping Certain Regions to Certain Countries in PowerQuery

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |

View solution in original post

Darko_Giac Regular Visitor
Regular Visitor

Re: Mapping Certain Regions to Certain Countries in PowerQuery

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

 

D.

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)