cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

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,

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Resolver IV
Resolver IV

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

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

View solution in original post

2 REPLIES 2
Highlighted
Resolver IV
Resolver IV

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

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

View solution in original post

Highlighted
New Member

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

Hi @BetterCallFrank

 

Thanks for your quick respond.

Work perfectly thanks for you help.

 

Most appreciated.

 

 

Helpful resources

Announcements
Meet the 2020 Season 2 Power BI Super Users!

Meet the 2020 Season 2 Power BI Super Users!

Find out who's part of the program this season, and welcome the new Super Users.

August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Featured Data Story of The Month

Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

Power BI Dev Camp - Developing with .NET Core

Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.

Top Solution Authors
Top Kudoed Authors