cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 342 members 3,494 guests
Please welcome our newest community members: