Forum Discussion

ShanTheMan's avatar
ShanTheMan
New Member
9 years ago
Solved

Help with Filled Map - multiple value is location and colour saturation field

 

Hello,

 

I have an consultant database from an excel sheet and I have a field/column  called "Country expertise". The field is multi-entry where you can enter multiple countries. In the field if more than one country has been entered for a single consultant it will appear as follows in the same cell:

Zambia;

Jordan;

South Sudan;

 

What I am trying to do is use the Filled Map to display by  colour saturation to show the most countries mentioned. So if "Zambia" appeared the most times within Countries Expertise field/column it would have the highest saturation. 

 

Is there any way of for PowerBi to pickup the country names as separate values and count them? As when I put the field into Location and Color saturation it will up the raw field data. E.g(Zambia; Jordan; South Sudan;)

 

Thanks,

 

 

 

  • Hi ShanTheMan

     

    yes, you can do that with these steps:

     

    1) in Powerquery split the Country expertise column by separator

    2) unpivot all the columns that will be generated by 1) into one column

     

    Please make sure that the duplicate data that will be generated by 2) does not affect other parts of your data model

     

    e.g. if your data now looks like this

     

    Consultant | CountryExpertise

    Bob | Sudan;China;Brazil

    Peter | USA

     

    will look like this after 1):

     

    Consultant | CountryExpertise1 | CountryExpertise2 | CountryExpertise3

    Bob | Sudan | China | Brazil

    Peter | USA

     

    and finally like this after 2)

    Consultant | CountryExpertise

    Bob | Sudan

    Bob | China

    Bob | Brazil

    Peter | USA

     

    HTH,

    Frank

2 Replies

  • Hi ShanTheMan

     

    yes, you can do that with these steps:

     

    1) in Powerquery split the Country expertise column by separator

    2) unpivot all the columns that will be generated by 1) into one column

     

    Please make sure that the duplicate data that will be generated by 2) does not affect other parts of your data model

     

    e.g. if your data now looks like this

     

    Consultant | CountryExpertise

    Bob | Sudan;China;Brazil

    Peter | USA

     

    will look like this after 1):

     

    Consultant | CountryExpertise1 | CountryExpertise2 | CountryExpertise3

    Bob | Sudan | China | Brazil

    Peter | USA

     

    and finally like this after 2)

    Consultant | CountryExpertise

    Bob | Sudan

    Bob | China

    Bob | Brazil

    Peter | USA

     

    HTH,

    Frank