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 Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (3,739)