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